Count number of cells in column with a value of 0 (Zero) based on value in another column

[Ryanaj]

I want to count the number of cells in column (AF) with a value of 0 (Zero) based on a specific value being in column (AC) ....."A", "Px" or"P"

Using Excel 2003 Have tried Countif, Dcount & other possibilities without success. HELP !!!!!!!!!!

[JBeaucaire]

In Excel 2007+ it's easy with SUMIFS or COUNTIFS (that's not the same as SUMIF or COUNTIF). But in Excel 2003 you're stuck with SUMPRODUCT to do this counting, and you can't reference whole columns.

So, setting a specific number of rows to check:

=SUMPRODUCT(--($AF$1:$AF$100=0), --($AC$1:$AC$100="A"))
=SUMPRODUCT(--($AF$1:$AF$100=0), --($AC$1:$AC$100="Px"))
=SUMPRODUCT(--($AF$1:$AF$100=0), --($AC$1:$AC$100="P"))

[Richard Buttrey]

Hi,

Since you don't have Excel 2007 or later which has the more powerful COUNTIFS() - plural function you'll need to use the usual workaround of

=SUMPRODUCT((AF1:AF100=0)*(AC1:AC100="A"))

obviously changing the range and "A" as appropriate

[Ryanaj]

Great, I beter ask the obvious question ... Excell 2007 solution for the origional Thread ie countifs for AF =A, AC =0

[dredwolf]

Try
=COUNTIFS(AF:AF,"=0",AC:AC,"=A")

Hope this helps

[Ryanaj]

Perfect - Thank You

[dredwolf]

You are welcome