Sum based on specific condition

G

Guest

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.
 
P

Peo Sjoblom

You should have asked

=SUMPRODUCT(--(A1:A10>B1:B10),C1:C10)

adapt to fit accordingly to your range references
 
G

Guest

A helper column would do it, Try in E1


=IF(A1>B1,D1,0)

Drage down for the length of your data and sum the resultant column.

Mike
 
G

Guest

try:

=SUM(IF(A1:A9>B1:B9,D1:D9))

Enter with Ctrl+Shift+Enter

{} will appear round formula if entered correctly

HTH
 
G

Guest

Hi Wendy,

To do this type the following (for rows 1-10, replace as required)

=SUMIF(A1:A10,">"&B1:B10,D1:D10)
when you have typed this instead of typing enter, hold CTRL, SHIFT and press
enter. This will put curly brackets round it, and tells excel it is an array
formula

hope this helps,

Cheers

Dazza
 
G

Guest

Given your ranges in use (change in the actual formula), this should work
=SUMPRODUCT(--(A1:A9>B1:B9),--(D1:D9))

I get 5907.71 (only 1st 2 entries have value in column A > column B value).
 
G

Guest

A second way to do it would be to use a helper column in E. At E1 put:
=IF(A1>B1,D1,0)
fill that formula down the sheet and then put a =SUM(E1:E9) formula down in
E10. Might be easier to understand that way, at the expense of using another
column.
 
G

Guest

=SUMPRODUCT(--(A1:A9>B1:B9),(D1:D9))
The first part, --(A1:A9>B1:B9), generates a series of 1's and 0's, with the
1's where your condition is satisfied. The second part (D1:D9) is the series
of values you want conditionally added. Sumproduct multiplies corresponding
components of the two series and adds the results.
 
G

Guest

Hope this helps:
=SUMPRODUCT(--(A2:A10>B2:B10),D2:D10)

Adjust to suit, but remember, when making modifications, all the columns
have to be the same length when working with SUMPRODUCT.
 
G

Guest

Wendy said:
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).
Click to expand...

=sumproduct((D1:D9)*(A1:A9>B1:B9))


----- original posting -----
 
G

Guest

Hi Wendy,

I think the easiest way would be to put =IF(A1>B1,D1,"")
in helper column E and drag down to the end of your data,
and then sum column E. You can then hide column E or use
a different helper column way off to the right somewhere
or even on a separate sheet if you wish.

HTH
Martin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need to 'COUNT'... 5

Top