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

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

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

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
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"sharon t" wrote:

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

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

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)


"sharon t" wrote in message
...
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".

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

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.

"John Bundy" wrote:

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
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"sharon t" wrote:

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

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

Did you try my suggestion?

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


"sharon t" wrote in message
...

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.

"John Bundy" wrote:

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
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"sharon t" wrote:

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

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

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?
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"sharon t" wrote:


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.

"John Bundy" wrote:

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
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"sharon t" wrote:

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

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

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:G55013),D1:D550+F1:F550)

HTH,
Paul


"PCLIVE" wrote in message
...
Did you try my suggestion?

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


"sharon t" wrote in message
...

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.

"John Bundy" wrote:

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
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"sharon t" wrote:

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

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

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

"PCLIVE" wrote:

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)


"sharon t" wrote in message
...
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".

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

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)


"sharon t" wrote:

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

"PCLIVE" wrote:

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)


"sharon t" wrote in message
...
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".

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

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" wrote:

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:G55013),D1:D550+F1:F550)

HTH,
Paul


"PCLIVE" wrote in message
...
Did you try my suggestion?

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


"sharon t" wrote in message
...

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.

"John Bundy" wrote:

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
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"sharon t" wrote:

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

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

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

"JMB" wrote in message
...
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" wrote:

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:G55013),D1:D550+F1:F550)

HTH,
Paul


"PCLIVE" wrote in message
...
Did you try my suggestion?

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


"sharon t" wrote in message
...

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.

"John Bundy" wrote:

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
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"sharon t" wrote:

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

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

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!

"PCLIVE" wrote:

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

"JMB" wrote in message
...
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" wrote:

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:G55013),D1:D550+F1:F550)

HTH,
Paul


"PCLIVE" wrote in message
...
Did you try my suggestion?

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


"sharon t" wrote in message
...

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.

"John Bundy" wrote:

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
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"sharon t" wrote:

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

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

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

"sharon t" wrote:

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