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