A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sum based on specific condition



 
 
Thread Tools Display Modes
  #21  
Old June 6th, 2007, 11:33 AM posted to microsoft.public.excel.worksheet.functions
Tom
external usenet poster
 
Posts: 1,359
Default Sum based on specific condition

one way is to create another column in E and use the following in column E.
=IF(A1B1,D1,0)
Your could then sum column E.

Tom
"Wendy" wrote:

Need help...I am trying to sum only those values in column (D)
whose corresponding value in column (A) is greater than the value in
column (B).

(A) (B) (C) (D)
1. 6,942 292 0.81 5,614.84
2. 317 0 0.92 292.87
3. 6,207 20,374 7.64 47,421.48
4. 417 17,588 5.84 2,435.28
5. 43,921 108,193 1.01 44,525.47
6. 5,974 15,883 3.11 18,579.14
7. 1,834 7,062 1.79 3,283.57
8. 665 733 2.13 1,416.76
9. 21,855 24,149 1.70 37,153.00

Any suggestions? I've been struggling with this for the past few days.


--
Wendy

  #22  
Old June 6th, 2007, 01:55 PM posted to microsoft.public.excel.worksheet.functions
andy62
external usenet poster
 
Posts: 140
Default Sum based on specific condition

With your data starting in cell A1 (adjust formula if needed), I believe this
will work:

=SUMPRODUCT(--(A1:A9B1:B9),D19)

The function sumproduct multiplies the pairs together and then sums all
those products. The first argument evaluates whether the data in A is
greater then its partner in B; if so, the result is 1, and if not, the result
is 0. The second argument is just the data in D, which then gets multiplied
by the 1's and 0's, and then summed.

HTH

"Wendy" wrote:

Need help...I am trying to sum only those values in column (D)
whose corresponding value in column (A) is greater than the value in
column (B).

(A) (B) (C) (D)
1. 6,942 292 0.81 5,614.84
2. 317 0 0.92 292.87
3. 6,207 20,374 7.64 47,421.48
4. 417 17,588 5.84 2,435.28
5. 43,921 108,193 1.01 44,525.47
6. 5,974 15,883 3.11 18,579.14
7. 1,834 7,062 1.79 3,283.57
8. 665 733 2.13 1,416.76
9. 21,855 24,149 1.70 37,153.00

Any suggestions? I've been struggling with this for the past few days.


--
Wendy

  #23  
Old June 6th, 2007, 01:55 PM posted to microsoft.public.excel.worksheet.functions
Ben McBen
external usenet poster
 
Posts: 6
Default Sum based on specific condition

How about:

=SUMPRODUCT(D210,--(A2:A10B2:B10))

e =

"Wendy" wrote:

Need help...I am trying to sum only those values in column (D)
whose corresponding value in column (A) is greater than the value in
column (B).

(A) (B) (C) (D)
1. 6,942 292 0.81 5,614.84
2. 317 0 0.92 292.87
3. 6,207 20,374 7.64 47,421.48
4. 417 17,588 5.84 2,435.28
5. 43,921 108,193 1.01 44,525.47
6. 5,974 15,883 3.11 18,579.14
7. 1,834 7,062 1.79 3,283.57
8. 665 733 2.13 1,416.76
9. 21,855 24,149 1.70 37,153.00

Any suggestions? I've been struggling with this for the past few days.


--
Wendy

  #24  
Old June 6th, 2007, 02:38 PM posted to microsoft.public.excel.worksheet.functions
Barb Reinhardt
external usenet poster
 
Posts: 1,381
Default Sum based on specific condition

=SUMPRODUCT(--(A1:A9B1:B9),(D19))

Commit with CTRL SHIFT ENTER

HTH,
Barb Reinhardt

"Wendy" wrote:

Need help...I am trying to sum only those values in column (D)
whose corresponding value in column (A) is greater than the value in
column (B).

(A) (B) (C) (D)
1. 6,942 292 0.81 5,614.84
2. 317 0 0.92 292.87
3. 6,207 20,374 7.64 47,421.48
4. 417 17,588 5.84 2,435.28
5. 43,921 108,193 1.01 44,525.47
6. 5,974 15,883 3.11 18,579.14
7. 1,834 7,062 1.79 3,283.57
8. 665 733 2.13 1,416.76
9. 21,855 24,149 1.70 37,153.00

Any suggestions? I've been struggling with this for the past few days.


--
Wendy

  #25  
Old June 6th, 2007, 02:41 PM posted to microsoft.public.excel.worksheet.functions
andy62
external usenet poster
 
Posts: 140
Default Sum based on specific condition

I replied to this a while ago with a longer explanation, but don't see that
post yet. Here's the short version, but I can elaborate if needed. Assuming
your data starts in A!, try the following:

=SUMPRODUCT(--(A1:A9B1:B9),D19)

HTH

"Wendy" wrote:

Need help...I am trying to sum only those values in column (D)
whose corresponding value in column (A) is greater than the value in
column (B).

(A) (B) (C) (D)
1. 6,942 292 0.81 5,614.84
2. 317 0 0.92 292.87
3. 6,207 20,374 7.64 47,421.48
4. 417 17,588 5.84 2,435.28
5. 43,921 108,193 1.01 44,525.47
6. 5,974 15,883 3.11 18,579.14
7. 1,834 7,062 1.79 3,283.57
8. 665 733 2.13 1,416.76
9. 21,855 24,149 1.70 37,153.00

Any suggestions? I've been struggling with this for the past few days.


--
Wendy

  #26  
Old June 6th, 2007, 03:25 PM posted to microsoft.public.excel.worksheet.functions
Barb Reinhardt
external usenet poster
 
Posts: 1,381
Default Sum based on specific condition

I got a notification of a response to this thread, but there's nothing here.
Something very odd is going on.

"Wendy" wrote:

Need help...I am trying to sum only those values in column (D)
whose corresponding value in column (A) is greater than the value in
column (B).

(A) (B) (C) (D)
1. 6,942 292 0.81 5,614.84
2. 317 0 0.92 292.87
3. 6,207 20,374 7.64 47,421.48
4. 417 17,588 5.84 2,435.28
5. 43,921 108,193 1.01 44,525.47
6. 5,974 15,883 3.11 18,579.14
7. 1,834 7,062 1.79 3,283.57
8. 665 733 2.13 1,416.76
9. 21,855 24,149 1.70 37,153.00

Any suggestions? I've been struggling with this for the past few days.


--
Wendy

  #27  
Old June 6th, 2007, 04:16 PM posted to microsoft.public.excel.worksheet.functions
Tom Hutchins
external usenet poster
 
Posts: 722
Default Sum based on specific condition

Try =SUMPRODUCT(--((A1:A9)(B1:B9)),D19)

I posted this yesterday, but don't see it or any other replies, so I am
reposting it.

Hope this helps,

Hutch

"Wendy" wrote:

Need help...I am trying to sum only those values in column (D)
whose corresponding value in column (A) is greater than the value in
column (B).

(A) (B) (C) (D)
1. 6,942 292 0.81 5,614.84
2. 317 0 0.92 292.87
3. 6,207 20,374 7.64 47,421.48
4. 417 17,588 5.84 2,435.28
5. 43,921 108,193 1.01 44,525.47
6. 5,974 15,883 3.11 18,579.14
7. 1,834 7,062 1.79 3,283.57
8. 665 733 2.13 1,416.76
9. 21,855 24,149 1.70 37,153.00

Any suggestions? I've been struggling with this for the past few days.


--
Wendy

  #28  
Old June 6th, 2007, 04:24 PM posted to microsoft.public.excel.worksheet.functions
Amanda
external usenet poster
 
Posts: 335
Default Sum based on specific condition

Hi Wendy,

I've copied this table into Excel A1 : E10.

I'd add a 6th column (E): with this formula:

=IF(B2C2,"x","")

It will put an 'x' into any cell where ColumnHeadingA is greater than
ColumnHeadingB

Then I'd put the following total at the foot of ColumnHeadingD:

=SUMIF(F2:F10,"x",E2:E10)


I don't usually respond, so I hope this makes sense (and works!)

Kind regards

Amanda

"Wendy" wrote:

Need help...I am trying to sum only those values in column (D)
whose corresponding value in column (A) is greater than the value in
column (B).

(A) (B) (C) (D)
1. 6,942 292 0.81 5,614.84
2. 317 0 0.92 292.87
3. 6,207 20,374 7.64 47,421.48
4. 417 17,588 5.84 2,435.28
5. 43,921 108,193 1.01 44,525.47
6. 5,974 15,883 3.11 18,579.14
7. 1,834 7,062 1.79 3,283.57
8. 665 733 2.13 1,416.76
9. 21,855 24,149 1.70 37,153.00

Any suggestions? I've been struggling with this for the past few days.


--
Wendy

  #29  
Old June 6th, 2007, 04:29 PM posted to microsoft.public.excel.worksheet.functions
David Biddulph
external usenet poster
 
Posts: 8,714
Default Sum based on specific condition

The "something very odd" which is going on is that Microsoft's web interface
to the newsgroups is broken.

You'll see the messages if you use a newsreader or Google's archives.

But as all the people who are asking the questions about this problem are
the people who are using the Microsoft web interface, they are not seeing
the replies to their questions. :-(
--
David Biddulph

"Barb Reinhardt" wrote in message
...
I got a notification of a response to this thread, but there's nothing
here.
Something very odd is going on.

....


  #30  
Old June 6th, 2007, 06:35 PM posted to microsoft.public.excel.worksheet.functions
ray
external usenet poster
 
Posts: 475
Default Sum based on specific condition

Here's one way
(A) (B) (C) (D) (E)
1 6942 292 0.81 5614.84 =IF(B2C2,E2,"")
2 317 0 0.92 292.87 =IF(B3C3,E3,"")
3 6207 20374 7.64 47421.48 =IF(B4C4,E4,"")
4 417 17588 5.84 2435.28 =IF(B5C5,E5,"")
5 43921 108193 1.01 44525.47 =IF(B6C6,E6,"")
6 5974 15883 3.11 18579.14 =IF(B7C7,E7,"")
7 1834 7062 1.79 3283.57 =IF(B8C8,E8,"")
8 665 733 2.13 1416.76 =IF(B9C9,E9,"")
9 21855 24149 1.7 37153 =IF(B10C10,E10,"")
=SUM(F2:F10)


"Wendy" wrote:

Need help...I am trying to sum only those values in column (D)
whose corresponding value in column (A) is greater than the value in
column (B).

(A) (B) (C) (D)
1. 6,942 292 0.81 5,614.84
2. 317 0 0.92 292.87
3. 6,207 20,374 7.64 47,421.48
4. 417 17,588 5.84 2,435.28
5. 43,921 108,193 1.01 44,525.47
6. 5,974 15,883 3.11 18,579.14
7. 1,834 7,062 1.79 3,283.57
8. 665 733 2.13 1,416.76
9. 21,855 24,149 1.70 37,153.00

Any suggestions? I've been struggling with this for the past few days.


--
Wendy

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:16 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.