COUNTIF for 0s and blanks

[goosteroo]

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


This formula doesn't work. Can someone please help?

Many thanks in advance.

 

[DominicB]

Good morning goosteroo

How about this :

=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

 

[barry houdini]

Perhaps

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

 

[goosteroo]

I used the sumproduct, although the calculations take longer.

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

 

[Scott Huish]

How didn't the COUNTIF formula work?

Book2
	A	B	C	D	E	F	G	H
1	1	0	0	2	3			3
Sheet1

 

[barry houdini]

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")

 

[Aladin Akyurek]

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


This formula doesn't work. Can someone please help?

Many thanks in advance.

Given your sample...

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