Countif 2 variables

[tdm1103]

Hi,

I have a row of 24 values. I am looking to do a COUNTIF function that counts all cells that have a value that are greater than 1 and are preceded by "0". For example:

0 0 4 5 2 0 0 0 6 0 0 0 7 8 9 6 0 0 0 0 3 4 5 0

In the string of the numbers above, the COUNTIF function would return a value of 4.

Thanks

 

[njimack]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	0	0	4	5	2	0	0	0	6	0	0	0	7	8	9	6	0	0	0	0	3	4	5	0		4
Sheet1

 

[tdm1103]

Thanks for the quick reply. Why in your formula in the first expression do you not include A1? There are instances where A1 will be greater than 0.

 

[Aladin Akyurek]

Hi,

I have a row of 24 values. I am looking to do a COUNTIF function that counts all cells that have a value that are greater than 1 and are preceded by "0". For example:

0 0 4 5 2 0 0 0 6 0 0 0 7 8 9 6 0 0 0 0 3 4 5 0

In the string of the numbers above, the COUNTIF function would return a value of 4.

Thanks

Sheet1

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	0	0	4	5	2	0	0	0	6	0	0	0	7	8	9	6	0	0	0	0	3	4	5	0		4

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 8px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula Z1 =SUMPRODUCT(--(B1:X1>1),--(A1:W1=0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

In case you want to ignore empty cells...

=SUMPRODUCT(--(B1:X1>1),--(A1:W1=0),--ISNUMBER(A1:W1))

 

[tdm1103]

Sorry for my ignorance, but are the "--" before each range placeholders for something or is that what I use?

 

[T. Valko]

Hi,

I have a row of 24 values. I am looking to do a COUNTIF function that counts all cells that have a value that are greater than 1 and are preceded by "0". For example:

0 0 4 5 2 0 0 0 6 0 0 0 7 8 9 6 0 0 0 0 3 4 5 0

In the string of the numbers above, the COUNTIF function would return a value of 4.

Thanks

If you're using Excel 2007 or later:

=COUNTIFS(B1:X1,">1",A1:W1,0)

 

[tdm1103]

This makes more sense, I am using Excel 2010.

 

[Aladin Akyurek]

Sorry for my ignorance, but are the "--" before each range placeholders for something or is that what I use?

Some links on the matter are:

http://www.mrexcel.com/forum/showthread.php?202204
http://www.mrexcel.com/forum/showthread.php?70547
http://www.mrexcel.com/forum/showthread.php?339081

 

[njimack]

Thanks for the quick reply. Why in your formula in the first expression do you not include A1? There are instances where A1 will be greater than 0.

But A1 can never be preceded by a zero, since it's in the first column of the spreadsheet.

 

[tdm1103]

Figured that out, I just added an "IF" statement after my COUNTIFS statement to add 1 if A1 is greater than 0.