Sum if and with multiple criteria
 

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

Sum if and with multiple criteria
 

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)
--
If this post helps click Yes
---------------
Jacob Skaria


"Micki" wrote:

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

Sum if and with multiple criteria
 

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

"Micki" wrote:

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

Sum if and with multiple criteria
 

Check col C's values. There's probably some text/error values within. Clear
it up and it should work fine.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Micki" wrote:
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))

Sum if and with multiple criteria
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Micki" wrote in message
...
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))

Sum if and with multiple criteria
 

does

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

help?


On 21 Maj, 14:01, Micki wrote:
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))

Sum if and with multiple criteria
 

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

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Micki" wrote in message
...
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))

Sum if and with multiple criteria
 

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

If this post helps click Yes
---------------
Jacob Skaria


"Micki" wrote:

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

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Micki" wrote in message
...
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))

Sum if and with multiple criteria
 

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

should handle those ""



On 21 Maj, 14:37, Micki wrote:
Some cells contained a value of "", I made them zeros and it worked. Thanks
everyone.



"Don Guillett" wrote:
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Micki" wrote in message
...
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))- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Sum if and with multiple criteria
 

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


"Jarek Kujawa" wrote in message
...
=SUMPRODUCT((--($A$6:$A$488="H"))*(--($B$6:$B$488="B1")*(LEN(C$6:C$488)
0),C$6:C$488)

should handle those ""



On 21 Maj, 14:37, Micki wrote:
Some cells contained a value of "", I made them zeros and it worked.
Thanks
everyone.



"Don Guillett" wrote:
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Micki" wrote in message
...
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))-
Ukryj cytowany tekst -

- Pokaz cytowany tekst -