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

[Guest]

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

 

[Guest]

tell me if this does what you ask
=IF(OR(E1=14, G1=14),IF(AND(C1="reason",A1=1),SUM(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

 

[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),D2100+F2:F100)

 

[Guest]

This is close but...

I need to sum column D1550 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.

 

[PCLIVE]

Did you try my suggestion?

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

 

[Guest]

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?

 

[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),D1550+F1:F550)

HTH,
Paul

 

[Guest]

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

 

[Guest]

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),D2100+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),D2100)
and a separate total for column F?
=SUMPRODUCT(--(A2:A100=1),--(C2:C100="red"),--((E2:E100=14)+(G2:G100=14)>0),F2:F100)

 

[Guest]

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.

 

[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

 

[Guest]

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!

 

[Guest]

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))