Counting zero's in a column based on criteria from an additional column....

[blackbird4823]

Hope everyone is having a great Monday (if there is such a thing.)

I am trying to count the number of 0 thru 9 in a column based on criteria (non-blank cell) to the right of the cell.

This is an example from the sheet:
H5:H275 I5:I275
blank blank
70 B57
0 B57
5 B57
0 B57
11 B57
29 A21
0 blank
0 blank
0 blank


total should be 3

SOME of the things I have tried:

=SUM(COUNTIF(H5:H275,"<9",H5:H275,">=0"),COUNTIF(I:I,>" ")
=COUNT(H:H,"<9")
=SUMPRODUCT(--(H:H<10),--(I:I>" "))
=SUMPRODUCT(--(H:H<10),(H:H,">=0)--(I:I>" "))
=SUMPRODUCT(--(H5:H275<10),(H5:H275>=0))-COUNT(I5:I275," ")
=SUMPRODUCT(--($H$5:$H$275=<0),--($H$5:$H$275=<10))
=SUMPRODUCT(--($H$5:$H$275>=0),--($H$5:$H$275<10))-COUNT(I5:I275," ")]

none of which have worked.

ANY and ALL help greatly appreciated!!!

 

[barry houdini]

Try this version

=COUNTIFS(H5:H275,"<=9",H5:H275,">=0",I5:I275,"<>")

 

[Amberv83]

Have you tried creating a pivot table?

 

[blackbird4823]

Mr Houdini,

what is sad is I had this number once before and I thought it was wrong because I was counting ONLY the zeros when I was verifying my formula. That just shows >>I<< am not having a good Monday.

Thank you for your precious time and reply. Hope YOU have a great Monday.