Count IF Multiple conditions

**buggzero** · 08-24-2012, 04:26 PM

I understand that the COUNTIF Formula can only be used for one condition. On my current project, I have two criteria that need to be met. The H Column needs to have a non-blank value and The C Column needs to have SITE A.

I have structured my formula like this:

=COUNT(IF(H8:H407>0,IF(C8:407="SITE A",1,0),0))

However the formula just returns 400 everytime... what am I doing wrong? This is in Excel2003

**Ace_XL** · 08-24-2012, 04:35 PM

=SUMPRODUCT((H8:H407>0)*(C8:C407="SITE A")) should do it in 2003 version.

In later versions, you have the COUNTIFS function too! Time for an upgrade?

**buggzero** · 08-24-2012, 04:44 PM

Haha! I Wish! only the lucky ones get the 2007 license. I am the lowly Janitor, cleaning up messes others have left behind.

Now this formula is returning all cells that have met the "SITE A" condition, but not that meet BOTH conditions.=\

**Ace_XL** · 08-24-2012, 04:48 PM

Aha, perhaps by non-blank you really mean 'non-blank' and not '>0' (which I took from your formula)
Try
=SUMPRODUCT((H8:H407<>"")*(C8:C407="SITE A"))

If this does not work, kindly upload a sample!

**buggzero** · 08-24-2012, 04:55 PM

Boom! That did it, I mean they are zeros, and its all numerical data, but for some reason it doesn't register as a real zero... I don't get it. HOWEVER YOU SIR, are awesome, Thank you very much :D