# COUNTIF for 0s and blanks

#### goosteroo

##### New Member
Hi, I have a formula that's supposed to count only if the cell doesn't include a "0" or a blank.

=COUNTIF(A1:G1,AND("<>0","<>"""))

The row looks like this

A B C E F G
1 0 0 2 3

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Good morning goosteroo

=SUMPRODUCT(--(Sheet1!\$A\$1:\$F\$1<>0),--(Sheet1!\$A\$1:\$F\$1<>""))

To construct formulae like this to conditionally add / count cells with up to 6 conditions you can download my add in via the link below which has a Multiple SumIf Generator - it builds the formulae on the screen as you specify the conditions.

HTH

DominicB

Perhaps

=COUNTIF(A1:G1,"<>")-COUNTIF(A1:G1,0)

I used the sumproduct, although the calculations take longer.

=COUNTIF(A1:G1,"<>")-COUNTIF(A1:G1,0) didn't work for me.

How didn't the COUNTIF formula work?
Book2
ABCDEFGH
1100233
Sheet1

I assume you have "formula blanks" in the range......

of course, given your example, if you only have zeroes, positive numbers or blanks

=COUNTIF(A1:G1,">0")

Hi, I have a formula that's supposed to count only if the cell doesn't include a "0" or a blank.

=COUNTIF(A1:G1,AND("<>0","<>"""))

The row looks like this

A B C E F G
1 0 0 2 3

=COUNT(A1:G1)-COUNTIF(A1:G1,0)

Replies
5
Views
741
Replies
2
Views
93
Replies
12
Views
238
Replies
3
Views
317
Replies
3
Views
239

1,217,328
Messages
6,135,917
Members
449,972
Latest member
Natejack

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

### Which adblocker are you using?

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

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