CountIf in Even Rows only

[Otto Moehrbach]

Excel 2007
I want to count the number of cells in a range that, say, are over 15, but I
want to consider only cells in even rows.
Also, I need help with counting cells that are, say, 14 to 15. Thanks for
your time. Otto

 

[Rick Rothstein]

Try these...

Over 15: =COUNTIF(A:A,">15")

Between and including 14 and 15: =COUNTIF(A:A,">=14")-COUNTIF(A:A,">15")

 

[Rick Rothstein]

Sorry, I forgot about the "even rows" condition. Try it this way...

Over 15
=========
=SUMPRODUCT((MOD(ROW(A:A),2)=0)*(A:A>15))

Between 14 and 15
===================
=SUMPRODUCT((MOD(ROW(A:A),2)=0)*(A:A>=14)*(A:A<=15))

 

[Harlan Grove]

Sorry, I forgot about the "even rows" condition. Try it this way...

Over 15
=========
=SUMPRODUCT((MOD(ROW(A:A),2)=0)*(A:A>15))

Between 14 and 15
===================
=SUMPRODUCT((MOD(ROW(A:A),2)=0)*(A:A>=14)*(A:A<=15))

These require Excel 2007. Excel 2003 and prior choke on entire column
references in array formulas.

FWIW, the between 14 and 15 formula on A3:A102 could be calculated
using

=SUMPRODUCT(MOD(ROW(A3:A102)-1,2)*(ABS(A3:A102-14.5)<=0.5))

 

[Rick Rothstein]

Sorry, I forgot about the "even rows" condition. Try it this way...

These require Excel 2007. Excel 2003 and prior choke on entire column
references in array formulas.

FWIW, the between 14 and 15 formula on A3:A102 could be calculated
using

=SUMPRODUCT(MOD(ROW(A3:A102)-1,2)*(ABS(A3:A102-14.5)<=0.5))

The OP said he was using XL2007 in the first line of his post.

 

[Bob Reynolds]

This works great for a project I'm working on BUT
how would I calculate some different ones specifically
if the group I was looking for was
less than 14
between 16 & 17
= to or greater than 18

Thanks so much
Bob Reynolds