How do you use countif for values in every four cell in a row
 

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

How do you use countif for values in every four cell in a row
 

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

How do you use countif for values in every four cell in a row
 

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

How do you use countif for values in every four cell in a row
 

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

How do you use countif for values in every four cell in a row
 

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

How do you use countif for values in every four cell in a row
 

-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

How do you use countif for values in every four cell in a row
 

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

How do you use countif for values in every four cell in a row
 

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

How do you use countif for values in every four cell in a row
 

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

How do you use countif for values in every four cell in a row
 

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.