count

B

Biff

Count based on what?

Here's one way:

=SUMPRODUCT(--(MOD(ROW(A1:A10)-ROW(A1),2)=0),--(ISNUMBER(A1:A10)))

Counts every other cell starting from A1 to A10 if it contains a number.

Biff
 
M

Max

Arby said:
how would you count every other cell?
Click to expand...

One interp ..

Count the # of alternate* cells in a column range, eg: A2:A11
*starting with the top cell in the range (ie A2)
=SUMPRODUCT(--(MOD(ROW(A2:A11),2)=0))

Count the # of alternate* cells in a row range, eg: B1:K1
*starting with the leftmost cell in the range (ie B1)
=SUMPRODUCT(--(MOD(COLUMN(B1:K1),2)=0))

And if you want to SUM what's in the alternating cells within the ranges
(instead of COUNT the number), just extend the above to:
=SUMPRODUCT(--(MOD(ROW(A2:A11),2)=0),A2:A11)
=SUMPRODUCT(--(MOD(COLUMN(B1:K1),2)=0),B1:K1)
 
A

Arby

Biff said:
Count based on what?

Here's one way:

=SUMPRODUCT(--(MOD(ROW(A1:A10)-ROW(A1),2)=0),--(ISNUMBER(A1:A10)))

Counts every other cell starting from A1 to A10 if it contains a number.

Biff




Thank you Biff--another question
Click to expand...
How would I count everyother non blank cell? Thx
 
A

Arby

Max said:
One interp ..

Count the # of alternate* cells in a column range, eg: A2:A11
*starting with the top cell in the range (ie A2)
=SUMPRODUCT(--(MOD(ROW(A2:A11),2)=0))

Count the # of alternate* cells in a row range, eg: B1:K1
*starting with the leftmost cell in the range (ie B1)
=SUMPRODUCT(--(MOD(COLUMN(B1:K1),2)=0))

And if you want to SUM what's in the alternating cells within the ranges
(instead of COUNT the number), just extend the above to:
=SUMPRODUCT(--(MOD(ROW(A2:A11),2)=0),A2:A11)
=SUMPRODUCT(--(MOD(COLUMN(B1:K1),2)=0),B1:K1)
Click to expand...
Ths Max--How would you count everyother nonblank cell?
 
M

Max

Just make it as:

=SUMPRODUCT((MOD(ROW(A2:A11),2)=0)*(A2:A11<>""))
=SUMPRODUCT((MOD(COLUMN(B1:K1),2)=0)*(B1:K1<>""))

("add" in the extra condition to check for non-blanks within the range)
 
A

Arby

Biff said:
=SUMPRODUCT(--(MOD(ROW(A1:A10)-ROW(A1),2)=0),--(A1:A10<>""))

Biff




I am getting a circular refence error when i replace A1:A10 with
Click to expand...
H3:H795 which is my range--any ideas--Thx?
 
A

Arby

Max said:
Just make it as:

=SUMPRODUCT((MOD(ROW(A2:A11),2)=0)*(A2:A11<>""))
=SUMPRODUCT((MOD(COLUMN(B1:K1),2)=0)*(B1:K1<>""))

("add" in the extra condition to check for non-blanks within the range)
Click to expand...

I am getting a circular refence error when i replace A1:A10 with
H3:H795 which is my range--any ideas--Thx?
 
B

Biff

I am getting a circular refence error when i replace A1:A10 with
H3:H795 which is my range--any ideas--Thx?
Click to expand...

Move the formula from the referenced range. In other words, don't put the
formula in a cell inside the range H3:H795.

Biff
 
A

Arby

Max said:
Place the formula in a cell outside the range
- this is the normal assumption <g>
Click to expand...

That works despite myself but it appears I need a formula to count all even
rows of non blank cells and one that counts all odd rows of non blank
cells--any ideas Thx
 
A

Arby

Biff said:
Move the formula from the referenced range. In other words, don't put the
formula in a cell inside the range H3:H795.

Biff




That works despite myself but it appears I need a formula to count all even rows of non blank cells and one that counts all odd rows of non blank cells--any ideas Thx
Click to expand...
 
B

Biff

For the odd numbered rows:

=SUMPRODUCT(--(MOD(ROW(H3:H795)-ROW(H3),2)=0),--(H3:H795<>""))

For the even numbered rows:

=SUMPRODUCT(--(MOD(ROW(H3:H795)-ROW(H3),2)=1),--(H3:H795<>""))

Biff
 
A

Arby

Biff said:
For the odd numbered rows:

=SUMPRODUCT(--(MOD(ROW(H3:H795)-ROW(H3),2)=0),--(H3:H795<>""))

For the even numbered rows:

=SUMPRODUCT(--(MOD(ROW(H3:H795)-ROW(H3),2)=1),--(H3:H795<>""))

Biff




Awesome-perfect--thank you!!
Click to expand...
 
M

Max

Similar to Biff's reply (.. feel like I'm in a parallel tango here <g>), just
play around with the result from the MOD(...,2) part of it in the formula, eg
MOD(...,2)=0, MOD(...,2)=1.
 
Top