| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif or sum production - 3 or more criteria
Excerpt from my s/s
A C I N pipe steel no grade size grade jonts result 4 J-55 100 white 4 J-55 50 yellow 5 N-80 50 blue 4 N-80 75 green I want to sum from column I based on criteria in cols. A,C & N. I have tried this: =SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) but it yields a 0 result. I tried the sumproduct method: =SUMPRODUCT(--(A6:A9=4)--(C6:C9="j-55")--(O6:O9="yellow"),(I6:I9)) It yielded 375 which is incorrect. With more than 2 criteria since it is an array it counts the TRUE's for a criteria and multiplies by the corresponding value in the I column instead of summing in I if all 3 criteria are met. The correct answer for my criteria set is 50. Suggestions?? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif or sum production - 3 or more criteria
Your SUM(IF(... has incorrect syntax -- it still needs to follow the
condition, value if true, value if false. You're close with the SUMPRODUCT - just missing commas: =SUMPRODUCT(--(A6:A9=4),--(C6:C9="j-55"),--(O6:O9="yellow"),(I6:I9)) the above should work "James" wrote in message ... Excerpt from my s/s A C I N pipe steel no grade size grade jonts result 4 J-55 100 white 4 J-55 50 yellow 5 N-80 50 blue 4 N-80 75 green I want to sum from column I based on criteria in cols. A,C & N. I have tried this: =SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) but it yields a 0 result. I tried the sumproduct method: =SUMPRODUCT(--(A6:A9=4)--(C6:C9="j-55")--(O6:O9="yellow"),(I6:I9)) It yielded 375 which is incorrect. With more than 2 criteria since it is an array it counts the TRUE's for a criteria and multiplies by the corresponding value in the I column instead of summing in I if all 3 criteria are met. The correct answer for my criteria set is 50. Suggestions?? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif or sum production - 3 or more criteria
James,
Try this =SUMPRODUCT((A6:A9=4)*(C6:C9="j-55")*(N6:N9="yellow")*(I6:I9)) Mike "James" wrote: Excerpt from my s/s A C I N pipe steel no grade size grade jonts result 4 J-55 100 white 4 J-55 50 yellow 5 N-80 50 blue 4 N-80 75 green I want to sum from column I based on criteria in cols. A,C & N. I have tried this: =SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) but it yields a 0 result. I tried the sumproduct method: =SUMPRODUCT(--(A6:A9=4)--(C6:C9="j-55")--(O6:O9="yellow"),(I6:I9)) It yielded 375 which is incorrect. With more than 2 criteria since it is an array it counts the TRUE's for a criteria and multiplies by the corresponding value in the I column instead of summing in I if all 3 criteria are met. The correct answer for my criteria set is 50. Suggestions?? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif or sum production - 3 or more criteria
You have a right syntax on your first formula. All you have to commit with
ctrl+shift+enter, not just enter =SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) The second formula should be like this: =SUMPRODUCT(--(A6:A9=4),--(C6:C9="j-55"),--(N6:N9="yellow"),(I6:I9)) "James" wrote: Excerpt from my s/s A C I N pipe steel no grade size grade jonts result 4 J-55 100 white 4 J-55 50 yellow 5 N-80 50 blue 4 N-80 75 green I want to sum from column I based on criteria in cols. A,C & N. I have tried this: =SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) but it yields a 0 result. I tried the sumproduct method: =SUMPRODUCT(--(A6:A9=4)--(C6:C9="j-55")--(O6:O9="yellow"),(I6:I9)) It yielded 375 which is incorrect. With more than 2 criteria since it is an array it counts the TRUE's for a criteria and multiplies by the corresponding value in the I column instead of summing in I if all 3 criteria are met. The correct answer for my criteria set is 50. Suggestions?? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif or sum production - 3 or more criteria
thanks
Both suggetions (MH & TM) still yield a 0 result. The values in cells in column A are from a picklist with fixed decimal places. Could this be causing the problem? Seems I was reading something about not having any formatting in your data. "Teethless mama" wrote: You have a right syntax on your first formula. All you have to commit with ctrl+shift+enter, not just enter =SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) The second formula should be like this: =SUMPRODUCT(--(A6:A9=4),--(C6:C9="j-55"),--(N6:N9="yellow"),(I6:I9)) "James" wrote: Excerpt from my s/s A C I N pipe steel no grade size grade jonts result 4 J-55 100 white 4 J-55 50 yellow 5 N-80 50 blue 4 N-80 75 green I want to sum from column I based on criteria in cols. A,C & N. I have tried this: =SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) but it yields a 0 result. I tried the sumproduct method: =SUMPRODUCT(--(A6:A9=4)--(C6:C9="j-55")--(O6:O9="yellow"),(I6:I9)) It yielded 375 which is incorrect. With more than 2 criteria since it is an array it counts the TRUE's for a criteria and multiplies by the corresponding value in the I column instead of summing in I if all 3 criteria are met. The correct answer for my criteria set is 50. Suggestions?? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif or sum production - 3 or more criteria
check your data to make sure no leading and/or trailing spaces.
"James" wrote: thanks Both suggetions (MH & TM) still yield a 0 result. The values in cells in column A are from a picklist with fixed decimal places. Could this be causing the problem? Seems I was reading something about not having any formatting in your data. "Teethless mama" wrote: You have a right syntax on your first formula. All you have to commit with ctrl+shift+enter, not just enter =SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) The second formula should be like this: =SUMPRODUCT(--(A6:A9=4),--(C6:C9="j-55"),--(N6:N9="yellow"),(I6:I9)) "James" wrote: Excerpt from my s/s A C I N pipe steel no grade size grade jonts result 4 J-55 100 white 4 J-55 50 yellow 5 N-80 50 blue 4 N-80 75 green I want to sum from column I based on criteria in cols. A,C & N. I have tried this: =SUM(IF((A6:A9=4)*(C6:C9="J-55")*(N6:N9="yellow"),I6:I9)) but it yields a 0 result. I tried the sumproduct method: =SUMPRODUCT(--(A6:A9=4)--(C6:C9="j-55")--(O6:O9="yellow"),(I6:I9)) It yielded 375 which is incorrect. With more than 2 criteria since it is an array it counts the TRUE's for a criteria and multiplies by the corresponding value in the I column instead of summing in I if all 3 criteria are met. The correct answer for my criteria set is 50. Suggestions?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumif with 2 criteria | Excel Worksheet Functions | |||
| SUMIF with four criteria | Excel Worksheet Functions | |||
| Sumif 2 criteria | Excel Discussion (Misc queries) | |||
| nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
| Sumif() with criteria | Excel Worksheet Functions | |||
Linear Mode
