| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with Multiple Criteria
Hi All
I have a question which I cant figure out - I use Sumproduct alot to sum up multiple criteria and it works well in most situations I have no problem using multiple criteria for one range: =SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"}) *(C2:C95)) give me 1268 - correct number =SUMPRODUCT((B2:B95={"01","02","03","04"})*(C2:C95 )) give me 1268 - correct number =SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"}) *(B2:B95={"04"})*(C2:C95)) - give me 1171 - correct number However....... =SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"}) *(B2:B95={"03","04"})*(C2:C95)) give me "N/A" and more confusing.... =SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"}) *(B2:B95={"01","02","03","04"})*(C2:C95)) - gives me 341 - not even sure where that number comes from So - can I do what I am attempting to do which is use multiple ranges with multiple criteria in each range - any help on this would be greatly appreciated If you need any other information please let me know Thanks in advance for your thoughts on this Jeff A B C Qtr5 04 31 Qtr5 03 21 Qtr5 03 2 Qtr5 04 8 Qtr5 02 1 Qtr5 04 38 Qtr5 04 3 Qtr5 04 10 Qtr5 04 13 Qtr5 04 17 Qtr5 04 1 Qtr5 04 19 Qtr5 04 1 Qtr5 04 31 Qtr5 04 3 Qtr5 04 2 Qtr5 04 1 Qtr5 04 67 Qtr5 04 2 Qtr5 04 13 Qtr5 04 2 Qtr5 04 42 Qtr5 04 3 Qtr6 04 26 Qtr6 03 31 Qtr6 04 1 Qtr6 04 1 Qtr6 04 1 Qtr6 04 25 Qtr6 04 1 Qtr6 04 26 Qtr6 04 1 Qtr6 04 17 Qtr6 04 3 Qtr6 04 23 Qtr6 04 2 Qtr6 04 12 Qtr6 04 2 Qtr6 04 6 Qtr6 04 1 Qtr6 04 29 Qtr6 04 1 Qtr6 02 2 Qtr6 02 1 Qtr6 03 2 Qtr6 03 1 Qtr6 04 21 Qtr6 04 1 Qtr6 04 12 Qtr6 04 2 Qtr6 04 30 Qtr6 04 1 Qtr7 04 71 Qtr7 04 1 Qtr7 03 1 Qtr7 04 1 Qtr7 04 13 Qtr7 04 44 Qtr7 04 1 Qtr7 04 12 Qtr7 04 2 Qtr7 03 1 Qtr7 04 10 Qtr7 04 8 Qtr7 04 24 Qtr7 04 48 Qtr7 03 1 Qtr7 04 8 Qtr7 04 1 Qtr7 04 101 Qtr7 04 2 Qtr7 04 13 Qtr7 04 41 Qtr8 04 20 Qtr8 03 28 Qtr8 03 2 Qtr8 04 1 Qtr8 04 11 Qtr8 04 42 Qtr8 04 3 Qtr8 04 15 Qtr8 04 8 Qtr8 04 18 Qtr8 04 1 Qtr8 04 8 Qtr8 04 2 Qtr8 03 1 Qtr8 04 1 Qtr8 04 13 Qtr8 02 1 Qtr8 03 1 Qtr8 04 15 Qtr8 04 29 Qtr8 04 31 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with Multiple Criteria
Problem is that you are creating multi-column arrays, and multiplying by a
different size multi-column array (use formula auditing to see what I mean). To accomplish your task, try this: =SUMPRODUCT(((A2:A7="Qtr8")+(A2:A7="Qtr7")+(A2:A7= "Qtr6")+(A2:A7="Qtr5"))*((B2:B7="03")+(B2:B7="04") )*(C2:C7)) -- Best Regards, Luke M "JeffTO" wrote in message ... Hi All I have a question which I cant figure out - I use Sumproduct alot to sum up multiple criteria and it works well in most situations I have no problem using multiple criteria for one range: =SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"}) *(C2:C95)) give me 1268 - correct number =SUMPRODUCT((B2:B95={"01","02","03","04"})*(C2:C95 )) give me 1268 - correct number =SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"}) *(B2:B95={"04"})*(C2:C95)) - give me 1171 - correct number However....... =SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"}) *(B2:B95={"03","04"})*(C2:C95)) give me "N/A" and more confusing.... =SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"}) *(B2:B95={"01","02","03","04"})*(C2:C95)) - gives me 341 - not even sure where that number comes from So - can I do what I am attempting to do which is use multiple ranges with multiple criteria in each range - any help on this would be greatly appreciated If you need any other information please let me know Thanks in advance for your thoughts on this Jeff A B C Qtr5 04 31 Qtr5 03 21 Qtr5 03 2 Qtr5 04 8 Qtr5 02 1 Qtr5 04 38 Qtr5 04 3 Qtr5 04 10 Qtr5 04 13 Qtr5 04 17 Qtr5 04 1 Qtr5 04 19 Qtr5 04 1 Qtr5 04 31 Qtr5 04 3 Qtr5 04 2 Qtr5 04 1 Qtr5 04 67 Qtr5 04 2 Qtr5 04 13 Qtr5 04 2 Qtr5 04 42 Qtr5 04 3 Qtr6 04 26 Qtr6 03 31 Qtr6 04 1 Qtr6 04 1 Qtr6 04 1 Qtr6 04 25 Qtr6 04 1 Qtr6 04 26 Qtr6 04 1 Qtr6 04 17 Qtr6 04 3 Qtr6 04 23 Qtr6 04 2 Qtr6 04 12 Qtr6 04 2 Qtr6 04 6 Qtr6 04 1 Qtr6 04 29 Qtr6 04 1 Qtr6 02 2 Qtr6 02 1 Qtr6 03 2 Qtr6 03 1 Qtr6 04 21 Qtr6 04 1 Qtr6 04 12 Qtr6 04 2 Qtr6 04 30 Qtr6 04 1 Qtr7 04 71 Qtr7 04 1 Qtr7 03 1 Qtr7 04 1 Qtr7 04 13 Qtr7 04 44 Qtr7 04 1 Qtr7 04 12 Qtr7 04 2 Qtr7 03 1 Qtr7 04 10 Qtr7 04 8 Qtr7 04 24 Qtr7 04 48 Qtr7 03 1 Qtr7 04 8 Qtr7 04 1 Qtr7 04 101 Qtr7 04 2 Qtr7 04 13 Qtr7 04 41 Qtr8 04 20 Qtr8 03 28 Qtr8 03 2 Qtr8 04 1 Qtr8 04 11 Qtr8 04 42 Qtr8 04 3 Qtr8 04 15 Qtr8 04 8 Qtr8 04 18 Qtr8 04 1 Qtr8 04 8 Qtr8 04 2 Qtr8 03 1 Qtr8 04 1 Qtr8 04 13 Qtr8 02 1 Qtr8 03 1 Qtr8 04 15 Qtr8 04 29 Qtr8 04 31 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with Multiple Criteria
Try it like this...
=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A95,{"Qtr8","Qtr7","Qtr6","Qtr5 "},0))),--(ISNUMBER(MATCH(B2:B95,{"01","02","03","04"},0))), C2:C95) Or, better to use cells to hold the criteria... G2:G5 = Qtr8, Qtr7, Qtr6, Qtr5 H2:H5 = 01, 02, 03, 04 =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A95,G2:G5,0))),--(ISNUMBER(MATCH(B2:B95,H2:H5,0))),C2:C95) -- Biff Microsoft Excel MVP "JeffTO" wrote in message ... Hi All I have a question which I cant figure out - I use Sumproduct alot to sum up multiple criteria and it works well in most situations I have no problem using multiple criteria for one range: =SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"}) *(C2:C95)) give me 1268 - correct number =SUMPRODUCT((B2:B95={"01","02","03","04"})*(C2:C95 )) give me 1268 - correct number =SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"}) *(B2:B95={"04"})*(C2:C95)) - give me 1171 - correct number However....... =SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"}) *(B2:B95={"03","04"})*(C2:C95)) give me "N/A" and more confusing.... =SUMPRODUCT((A2:A95={"Qtr8","Qtr7","Qtr6","Qtr5"}) *(B2:B95={"01","02","03","04"})*(C2:C95)) - gives me 341 - not even sure where that number comes from So - can I do what I am attempting to do which is use multiple ranges with multiple criteria in each range - any help on this would be greatly appreciated If you need any other information please let me know Thanks in advance for your thoughts on this Jeff A B C Qtr5 04 31 Qtr5 03 21 Qtr5 03 2 Qtr5 04 8 Qtr5 02 1 Qtr5 04 38 Qtr5 04 3 Qtr5 04 10 Qtr5 04 13 Qtr5 04 17 Qtr5 04 1 Qtr5 04 19 Qtr5 04 1 Qtr5 04 31 Qtr5 04 3 Qtr5 04 2 Qtr5 04 1 Qtr5 04 67 Qtr5 04 2 Qtr5 04 13 Qtr5 04 2 Qtr5 04 42 Qtr5 04 3 Qtr6 04 26 Qtr6 03 31 Qtr6 04 1 Qtr6 04 1 Qtr6 04 1 Qtr6 04 25 Qtr6 04 1 Qtr6 04 26 Qtr6 04 1 Qtr6 04 17 Qtr6 04 3 Qtr6 04 23 Qtr6 04 2 Qtr6 04 12 Qtr6 04 2 Qtr6 04 6 Qtr6 04 1 Qtr6 04 29 Qtr6 04 1 Qtr6 02 2 Qtr6 02 1 Qtr6 03 2 Qtr6 03 1 Qtr6 04 21 Qtr6 04 1 Qtr6 04 12 Qtr6 04 2 Qtr6 04 30 Qtr6 04 1 Qtr7 04 71 Qtr7 04 1 Qtr7 03 1 Qtr7 04 1 Qtr7 04 13 Qtr7 04 44 Qtr7 04 1 Qtr7 04 12 Qtr7 04 2 Qtr7 03 1 Qtr7 04 10 Qtr7 04 8 Qtr7 04 24 Qtr7 04 48 Qtr7 03 1 Qtr7 04 8 Qtr7 04 1 Qtr7 04 101 Qtr7 04 2 Qtr7 04 13 Qtr7 04 41 Qtr8 04 20 Qtr8 03 28 Qtr8 03 2 Qtr8 04 1 Qtr8 04 11 Qtr8 04 42 Qtr8 04 3 Qtr8 04 15 Qtr8 04 8 Qtr8 04 18 Qtr8 04 1 Qtr8 04 8 Qtr8 04 2 Qtr8 03 1 Qtr8 04 1 Qtr8 04 13 Qtr8 02 1 Qtr8 03 1 Qtr8 04 15 Qtr8 04 29 Qtr8 04 31 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple criteria for sumproduct | Excel Discussion (Misc queries) | |||
| SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
| Sumproduct multiple criteria | Excel Worksheet Functions | |||
| Sum with multiple criteria using SUMPRODUCT | Excel Worksheet Functions | |||
| multiple criteria in if or sumproduct | Excel Worksheet Functions | |||
Linear Mode
