Sum if and with multiple criteria

  • Thread starter Thread starter Micki
  • Start date Start date
M

Micki

Here's my data

Column A Column B Column C
H B1 10
I B2 12
J B2 15
K B3 14

I want to sum Column C and multiply times .02 if the values in Column A=H,
and the values in column B=B1. This formula works until I add the
multiplication factor, then I get a return of #VALUE
=SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),((C$6:C$488)*.02))
 
This should work as long as your dont have any error within C6:C488 range...

=SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),C$6:C$488*0.02)
 
This should work as long as your dont have any error within C6:C488 range...

=SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),C$6:C$488*0.02)
 
Hi,
Your formula works for me, if you are importing data in that columns do Text
to columns for each column
 
Hi,
Your formula works for me, if you are importing data in that columns do Text
to columns for each column
 
I didn't get an error with yours but maybe? Are your numbers numbers
=SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),C$6:C$488)*0.02
 
I didn't get an error with yours but maybe? Are your numbers numbers
=SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),C$6:C$488)*0.02
 
.... and of course you don't need the double unary minus -- if you have
multiplied with *.

--A*--B is the same as =A*B
--
David Biddulph


=SUMPRODUCT((--($A$6:$A$488="H"))*(--($B$6:$B$488="B1")*(LEN(C$6:C$488)
0),C$6:C$488)
Click to expand...

should handle those ""
 
.... and of course you don't need the double unary minus -- if you have
multiplied with *.

--A*--B is the same as =A*B
--
David Biddulph


=SUMPRODUCT((--($A$6:$A$488="H"))*(--($B$6:$B$488="B1")*(LEN(C$6:C$488)
0),C$6:C$488)
Click to expand...

should handle those ""
 
Back
Top