CountIf in Even Rows only
 

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

CountIf in Even Rows only
 

Try these...

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

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

--
Rick (MVP - Excel)


"Otto Moehrbach" wrote in message
...
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

CountIf in Even Rows only
 

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

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

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Try these...

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

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

--
Rick (MVP - Excel)


"Otto Moehrbach" wrote in message
...
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

CountIf in Even Rows only
 

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

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

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

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))

CountIf in Even Rows only
 

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

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

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))

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

--
Rick (MVP - Excel)

CountIf in Even Rows only
 

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
"Harlan Grove" wrote in message
...
"Rick Rothstein" wrote...
Sorry, I forgot about the "even rows" condition. Try it this way...

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

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))