SUMPRODUCT - 3 columns, 1 cell and ignore blanks

Hi All

I've got stuck on another SUMPRODUCT problem (just when I thought I was getting the hang of them too!)

I want to count how many times a colour, with the reason code 49 has been approved

So in N3 I want a formula to look in cell L3, find all the matching time in column C, check how many times it has a "49" next to it and then also how many times it matches to a "1" in column H

Then in O3 I want to do similar, amthing column E with "73" but for the final match from column H to match against "0", but I gather that Excel counts blank cells as zeroes too

I got to the part where I can get how many BLUEs there are with a reason 49, but now my brain has got confused!


Many thanks in advance

R

Hi raehippychick

Does this do it?

In N3

SUMPRODUCT(--($C$2:$C$50000=$L$3)*($E$2:$E$50000=49)*($H$2:$H$50000=1))

In O3

=SUMPRODUCT(--($C$3:$C$50000=$L$3)*($E$3:$E$50000=73)*($H$3:$H$50000=0))




Ed

Thankyou so much - that seems to worked perfectly!

You just saved my brain from exploding... I was going round in circles with that one!

R

Thanks for the feedback - I'd hate your brain to explode, the debris might just get this far!

Ed