How do you use COUNTIF to check certain cells in a row that have a value of
zero.
cell C4, cell G4, cell K4, cell O4, cell S4, ....etc

Hi!

Try this:

=SUMPRODUCT(--(MOD(COLUMN(C4:S4)+1,4)=0),--(ISNUMBER(C4:S4)),--(C4:S4=0))

Biff

"Ring eye" wrote in message
...
How do you use COUNTIF to check certain cells in a row that have a value
of
zero.
cell C4, cell G4, cell K4, cell O4, cell S4, ....etc

Biff,
I could not get it to work for me. I can use the COUNTIF if the columns are
side by side. I can't get it to work when I checking every 4th column. Any
other idea



"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(MOD(COLUMN(C4:S4)+1,4)=0),--(ISNUMBER(C4:S4)),--(C4:S4=0))

Biff

"Ring eye" wrote in message
...
How do you use COUNTIF to check certain cells in a row that have a value
of
zero.
cell C4, cell G4, cell K4, cell O4, cell S4, ....etc

I could not get it to work for me. I can use the COUNTIF if the columns
are
side by side. I can't get it to work when I checking every 4th column.
Any
other idea

Forget about Countif in this case unless you want to string a bunch of them
together like this:

=COUNTIF(C4,0)+COUNTIF(G4,0)+etc,etc.

When you say you could not get it to work, what exactly does that mean?

You want to count how many cells contain 0 right? Not how many cells are
blank, right?

Are you sure the 0's are numbers and are not TEXT?

Biff

"Ring eye" wrote in message
...
Biff,
I could not get it to work for me. I can use the COUNTIF if the columns
are
side by side. I can't get it to work when I checking every 4th column.
Any
other idea



"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(MOD(COLUMN(C4:S4)+1,4)=0),--(ISNUMBER(C4:S4)),--(C4:S4=0))

Biff

"Ring eye" wrote in message
...
How do you use COUNTIF to check certain cells in a row that have a
value
of
zero.
cell C4, cell G4, cell K4, cell O4, cell S4, ....etc

When columns are inserted before the data, calculations will be incorrect...

=SUMPRODUCT(--(MOD(COLUMN(C4:S4)-COLUMN(C4)+0,4)=0),--(ISNUMBER(C4:S4)),--(C4:S4=0))

is robust against such insertions.

Biff wrote:
Hi!

Try this:

=SUMPRODUCT(--(MOD(COLUMN(C4:S4)+1,4)=0),--(ISNUMBER(C4:S4)),--(C4:S4=0))

Biff

"Ring eye" wrote in message
...

How do you use COUNTIF to check certain cells in a row that have a value
of
zero.
cell C4, cell G4, cell K4, cell O4, cell S4, ....etc

-COLUMN(C4)+0

What's the +0 for?

Biff

"Aladin Akyurek" wrote in message
...
When columns are inserted before the data, calculations will be
incorrect...

=SUMPRODUCT(--(MOD(COLUMN(C4:S4)-COLUMN(C4)+0,4)=0),--(ISNUMBER(C4:S4)),--(C4:S4=0))

is robust against such insertions.

Biff wrote:
Hi!

Try this:

=SUMPRODUCT(--(MOD(COLUMN(C4:S4)+1,4)=0),--(ISNUMBER(C4:S4)),--(C4:S4=0))

Biff

"Ring eye" wrote in message
...

How do you use COUNTIF to check certain cells in a row that have a value
of
zero.
cell C4, cell G4, cell K4, cell O4, cell S4, ....etc

Biff,
Each cell that I am comparing is defined as a percentage 12.12%. After I
count the number of cells which with 0 percentage, I want to count the number
of cells which has greater than 0 percentage, then count the number of cells
with less than 0 (negative Percent).

"Biff" wrote:

I could not get it to work for me. I can use the COUNTIF if the columns
are
side by side. I can't get it to work when I checking every 4th column.
Any
other idea

Forget about Countif in this case unless you want to string a bunch of them
together like this:

=COUNTIF(C4,0)+COUNTIF(G4,0)+etc,etc.

When you say you could not get it to work, what exactly does that mean?

You want to count how many cells contain 0 right? Not how many cells are
blank, right?

Are you sure the 0's are numbers and are not TEXT?

Biff

"Ring eye" wrote in message
...
Biff,
I could not get it to work for me. I can use the COUNTIF if the columns
are
side by side. I can't get it to work when I checking every 4th column.
Any
other idea



"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(MOD(COLUMN(C4:S4)+1,4)=0),--(ISNUMBER(C4:S4)),--(C4:S4=0))

Biff

"Ring eye" wrote in message
...
How do you use COUNTIF to check certain cells in a row that have a
value
of
zero.
cell C4, cell G4, cell K4, cell O4, cell S4, ....etc

Hi!

All you need to do is change this portion of the formula depending on which
criteria you want:

(C4:S4=0))

The above will count 0's.

For greater than 0:

(C4:S40))

For less than 0:

(C4:S4<0))

You haven't explained what you meant when you said the formula did not work.

Biff

"Ring eye" wrote in message
...
Biff,
Each cell that I am comparing is defined as a percentage 12.12%. After
I
count the number of cells which with 0 percentage, I want to count the
number
of cells which has greater than 0 percentage, then count the number of
cells
with less than 0 (negative Percent).

"Biff" wrote:

I could not get it to work for me. I can use the COUNTIF if the columns
are
side by side. I can't get it to work when I checking every 4th column.
Any
other idea

Forget about Countif in this case unless you want to string a bunch of
them
together like this:

=COUNTIF(C4,0)+COUNTIF(G4,0)+etc,etc.

When you say you could not get it to work, what exactly does that mean?

You want to count how many cells contain 0 right? Not how many cells are
blank, right?

Are you sure the 0's are numbers and are not TEXT?

Biff

"Ring eye" wrote in message
...
Biff,
I could not get it to work for me. I can use the COUNTIF if the
columns
are
side by side. I can't get it to work when I checking every 4th
column.
Any
other idea



"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(MOD(COLUMN(C4:S4)+1,4)=0),--(ISNUMBER(C4:S4)),--(C4:S4=0))

Biff

"Ring eye" wrote in message
...
How do you use COUNTIF to check certain cells in a row that have a
value
of
zero.
cell C4, cell G4, cell K4, cell O4, cell S4, ....etc

Thanks alot Guys, It works great, Better than stringing COUNTIFs 27 times
to get one answer. Thanks again.

One Happy Customer

"Biff" wrote:

-COLUMN(C4)+0

What's the +0 for?

Biff

"Aladin Akyurek" wrote in message
...
When columns are inserted before the data, calculations will be
incorrect...

=SUMPRODUCT(--(MOD(COLUMN(C4:S4)-COLUMN(C4)+0,4)=0),--(ISNUMBER(C4:S4)),--(C4:S4=0))

is robust against such insertions.

Biff wrote:
Hi!

Try this:

=SUMPRODUCT(--(MOD(COLUMN(C4:S4)+1,4)=0),--(ISNUMBER(C4:S4)),--(C4:S4=0))

Biff

"Ring eye" wrote in message
...

How do you use COUNTIF to check certain cells in a row that have a value
of
zero.
cell C4, cell G4, cell K4, cell O4, cell S4, ....etc

Like a placeholder. If it's needed to start counting/summing, etc. from
the first Nth value/cell, just change to +1.

Biff wrote:
-COLUMN(C4)+0


What's the +0 for?

Biff

"Aladin Akyurek" wrote in message
...

When columns are inserted before the data, calculations will be
incorrect...

=SUMPRODUCT(--(MOD(COLUMN(C4:S4)-COLUMN(C4)+0,4)=0),--(ISNUMBER(C4:S4)),--(C4:S4=0))

is robust against such insertions.

Biff wrote:

Hi!

Try this:

=SUMPRODUCT(--(MOD(COLUMN(C4:S4)+1,4)=0),--(ISNUMBER(C4:S4)),--(C4:S4=0))

Biff

"Ring eye" wrote in message
...


How do you use COUNTIF to check certain cells in a row that have a value
of
zero.
cell C4, cell G4, cell K4, cell O4, cell S4, ....etc




--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.