Sum if and with multiple criteria

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))
 
J

Jacob Skaria

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)
 
E

Eduardo

Hi,
Your formula works for me, if you are importing data in that columns do Text
to columns for each column
 
D

Don Guillett

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
 
J

Jarek Kujawa

does

=SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),C$6:C$488))*.02

help?
 
M

Micki

Some cells contained a value of "", I made them zeros and it worked. Thanks
everyone.
 
J

Jacob Skaria

Fine. But Micki this should handle blanks as well...

If this post helps click Yes
 
D

David Biddulph

.... 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)

should handle those ""
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top