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)

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)

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)

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)

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)