| 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?
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". |
|
#4
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". |
|
#5
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". |
|
#6
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". |
|
#7
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". |
|
#8
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". |
|
#9
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". |
|
#10
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". |
|
#11
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) | |||
Hybrid Mode
