| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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". |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| summing column c based an column a criteria | Excel Worksheet Functions | |||
| Index/Match - Lookup based on multiple column criteria | Excel Worksheet Functions | |||
| Sum column information based on multiple criteria | Excel Worksheet Functions | |||
| calculate average in a column based on criteria in another column | Excel Discussion (Misc queries) | |||
| move contents of column C based on criteria related to column A | Excel Discussion (Misc queries) | |||
Linear Mode
