CountIf in Even Rows only

  • Thread starter Thread starter Otto Moehrbach
  • Start date Start date
O

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
 
Try these...

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

Between and including 14 and 15: =COUNTIF(A:A,">=14")-COUNTIF(A:A,">15")
 
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))
 
Rick Rothstein said:
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))
Click to expand...

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))
 
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))
Click to expand...

The OP said he was using XL2007 in the first line of his post.
 
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
 
Back
Top