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

#### blackbird4823

##### New Member
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!!!

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### barry houdini

##### MrExcel MVP
Try this version

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

#### Amberv83

##### New Member
Have you tried creating a pivot table?

#### blackbird4823

##### New Member
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.

Replies
16
Views
732
Replies
1
Views
162
Replies
0
Views
189
Replies
1
Views
328
Replies
2
Views
147

1,195,594
Messages
6,010,625
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back