Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple criteria
I have a very simple sumif with mulitple criteria, but I can't get the
correct result. This is my formula. Shouldn't the "*" indicate the "and" operator? =SUMIF($B$8:$B$135,($B$8:$B$135=GO143)*($J$8:$J$13 5=$GR$142),$ER$8:$ER$135) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple criteria
Try:
=SUMPRODUCT(($B$8:$B$135=GO143)*($J$8:$J$135=$GR$1 42),$ER$8:$ER$135) On Feb 2, 9:27 am, Tina wrote: I have a very simple sumif with mulitple criteria, but I can't get the correct result. This is my formula. Shouldn't the "*" indicate the "and" operator? =SUMIF($B$8:$B$135,($B$8:$B$135=GO143)*($J$8:$J$13 5=$GR$142),$ER$8:$ER$135) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple criteria
Perfect!
Would you please tell me why sumproduct works? I would never have thought to use sumproduct because I'm not multiplying anything, and I thought that's what sumproduct did. Thanks! "Lori" wrote: Try: =SUMPRODUCT(($B$8:$B$135=GO143)*($J$8:$J$135=$GR$1 42),$ER$8:$ER$135) On Feb 2, 9:27 am, Tina wrote: I have a very simple sumif with mulitple criteria, but I can't get the correct result. This is my formula. Shouldn't the "*" indicate the "and" operator? =SUMIF($B$8:$B$135,($B$8:$B$135=GO143)*($J$8:$J$13 5=$GR$142),$ER$8:$ER$135) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple criteria
Hi Tina
Sumproduct is producing arrays. The first term returns True or False, depending upon whether the cells in the B range that match GO143. The second term returns True or False depending upon whether the cells in the J range that match $GR$142. The third term represents the array of numbers you wish to sum in ER Multiplying the terms, coerces Trues to 1's and False's to 0's which sumproduct then calculates. 1 * 1 * 100 = 100 0 * 1 * 100 = 0 1 * 0 * 100 = 0 0 * 0 * 100 = 0 So only the values in ER where the results are True for the 2 tests get summed. For a more detailed explanation of how Sumproduct works take a look at Bob Philips excellent treatise on the subject at http://xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Roger Govier "Tina" wrote in message ... Perfect! Would you please tell me why sumproduct works? I would never have thought to use sumproduct because I'm not multiplying anything, and I thought that's what sumproduct did. Thanks! "Lori" wrote: Try: =SUMPRODUCT(($B$8:$B$135=GO143)*($J$8:$J$135=$GR$1 42),$ER$8:$ER$135) On Feb 2, 9:27 am, Tina wrote: I have a very simple sumif with mulitple criteria, but I can't get the correct result. This is my formula. Shouldn't the "*" indicate the "and" operator? =SUMIF($B$8:$B$135,($B$8:$B$135=GO143)*($J$8:$J$13 5=$GR$142),$ER$8:$ER$135) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple criteria
Ah ha! Thank you. Much appreciated.
"Roger Govier" wrote: Hi Tina Sumproduct is producing arrays. The first term returns True or False, depending upon whether the cells in the B range that match GO143. The second term returns True or False depending upon whether the cells in the J range that match $GR$142. The third term represents the array of numbers you wish to sum in ER Multiplying the terms, coerces Trues to 1's and False's to 0's which sumproduct then calculates. 1 * 1 * 100 = 100 0 * 1 * 100 = 0 1 * 0 * 100 = 0 0 * 0 * 100 = 0 So only the values in ER where the results are True for the 2 tests get summed. For a more detailed explanation of how Sumproduct works take a look at Bob Philips excellent treatise on the subject at http://xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Roger Govier "Tina" wrote in message ... Perfect! Would you please tell me why sumproduct works? I would never have thought to use sumproduct because I'm not multiplying anything, and I thought that's what sumproduct did. Thanks! "Lori" wrote: Try: =SUMPRODUCT(($B$8:$B$135=GO143)*($J$8:$J$135=$GR$1 42),$ER$8:$ER$135) On Feb 2, 9:27 am, Tina wrote: I have a very simple sumif with mulitple criteria, but I can't get the correct result. This is my formula. Shouldn't the "*" indicate the "and" operator? =SUMIF($B$8:$B$135,($B$8:$B$135=GO143)*($J$8:$J$13 5=$GR$142),$ER$8:$ER$135) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif with multiple criteria | Excel Discussion (Misc queries) | |||
SUMIF - multiple criteria in different columns | Excel Worksheet Functions | |||
SUMIF multiple criteria | Excel Discussion (Misc queries) | |||
SUMIF with multiple criteria | Excel Worksheet Functions | |||
SUMIF in multiple columns based on other criteria in Excel? | Excel Discussion (Misc queries) |