How do I sum column D and F based on multiple column criteria?

S

sharon t

I want to sum colums D and F based on specified criteria in colums A, C, E
and G.

Sum "Total # Br 1" and "Total # Br 2" (colums D and F) if colum A is "1",
column C is "reason", column E is "14" and/or Column column G is "14".
 
J

John Bundy

tell me if this does what you ask
=IF(OR(E1=14, G1=14),IF(AND(C1="reason",A1=1),SUM(D:D),""),"")
=IF(OR(E1=14, G1=14),IF(AND(C1="reason",A1=1),SUM(G:G),""),"")
that assumes data is in the first row.
if this doesn't quite get it let us know what is going wrong/or i left out
 
P

PCLIVE

If both columns E and G have to be 14, then one way:

=SUMPRODUCT(--(A2:A100=1),--(C2:C100="reason"),--(E2:E100=14),--(G2:G100=14),D2:D100+F2:F100)
 
S

sharon t

This is close but...

I need to sum column D1:D550 and column F1:F550 based on criteria in column
A,C,E and G rows 1 thru 550 for each of the four columns. What I have not
been able to do with Conditional Sum is to total the data in two columns
based on the criteria in the other 4 columns. Thanks.
 
P

PCLIVE

Did you try my suggestion?

=SUMPRODUCT(--(A1:A550=1),--(C1:C550="reason"),--(E1:E550=14),--(G1:G550=14),D1:D550+F1:F550)
 
J

John Bundy

Not sure i understand your need. Are you checking say column A,C,E and G for
row 1, and if it meets the criteria sum d and f 1-550 in say I1, then
repeating for row 2 etc? If that is the case then you could have the summed
data in many or even all rows, is that right? Or are you wanting to add d+f
for the row that you are checking from 1-550? or something else. If something
else could you post what a row or final cell would look like that has met the
criteria?
 
P

PCLIVE

If the criteria is met based on if the row in column E OR G (not AND) is
"14", then you can try the following:

=SUMPRODUCT(--(A1:A550=1),--(C1:C550="reason"),--(E1:E550+G1:G550>13),D1:D550+F1:F550)

HTH,
Paul
 
S

sharon t

I don't think I am being real clear on what I need. I'll see if I can clarify.

As in Conditional Sum where criteria is based on many critria (column A, row
1 thru 550) (column C, row 1 thru 550) (column E, row 1 thru 550) and (column
G, row 1 thru 550)

Total the numbers in column D and column F, row 1 thru 550, based on the
above criteria. So, for Br#1=14, Reason=red, Mo=1, Br #2=14 (sum of the 2
would be 1.5)

A B D E F
G
MO Reason Total Br #1 Br #1 Total Br #2
Br #2
1 1 red .5 27 .5
14
2 2 green .5 24 .5
3 7 red 1.0 24 .5
14
4 1 blue 1.0 32 1.0
5 7 green .5 27 .5
14
6 1 black .5 11 .5
24
7 1 red 1.0 14
 
J

JMB

A small change to PCLIVES suggestion to accomodate the and/or requirement for
columns E and G:

=SUMPRODUCT(--(A2:A100=1),--(C2:C100="red"),--((E2:E100=14)+(G2:G100=14)>0),D2:D100+F2:F100)

Do you want one total for both column D and F (which is what this formula
does) or one total for column D
=SUMPRODUCT(--(A2:A100=1),--(C2:C100="red"),--((E2:E100=14)+(G2:G100=14)>0),D2:D100)
and a separate total for column F?
=SUMPRODUCT(--(A2:A100=1),--(C2:C100="red"),--((E2:E100=14)+(G2:G100=14)>0),F2:F100)
 
J

JMB

If E1 is 12 and G1 is 2, then it will be included in the total. I think the
condition is that col E is 14 and/or col G is 14 not the sum of cols E and G
is >= 14.
 
P

PCLIVE

You're correct JMB. Originally it was not stated what other data might be
in columns E and G. That was a bad assumption on my part. However, the
solution you provided in your other post was what I was ultimately trying to
come up with (after the additional data was provided by the OP). Though in
their data provided, they listed one column as B instead of C, it was still
assumed that the column should have been C. Also, I'm sure you noticed that
the expected result of "1.5" does not match up to the result of what the OP
requested (the total of both columns E & G). My result came up with 2. I'm
sure that is why you listed one formula for totalling both columns and two
separate formulas to total individual columns.

Anyway, thanks for showing me the solution I was trying to come up with.
Regards,
Paul
 
J

JMB

I was a little confused about what data was in which column and I also could
not get where the 1.5 is coming from. And the NG word-wrap doesn't help any!
 
S

sharon t

I finally had time to get back on this project and finish it. Following is
the formula that I came up with and it works. Thanks all!

=SUM(IF('2007 Link'!$E$2:$E$800=44,IF('2007 Link'!$A$2:$A$800=1,IF('2007
Link'!$C$2:$C$800="red",'2007 Link'!$D$2:$D$800,0),0),0))+SUM(IF('2007
Link'!$G$2:$G$800=44,IF('2007 Link'!$A$2:$A$800=1,IF('2007
Link'!$C$2:$C$800="red",'2007 Link'!$F$2:$F$800,0),0),0))
 

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

Top