| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
count
how would you count everyother cell?
|
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
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 "Arby" wrote in message ... how would you count everyother cell? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
"Arby" wrote:
how would you count every other cell? 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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
"Biff" wrote: 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 "Arby" wrote in message ... how would you count everyother cell? Thank you Biff--another question How would I count everyother non blank cell? Thx |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
"Max" wrote: "Arby" wrote: how would you count every other cell? 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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Ths Max--How would you count everyother nonblank cell? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
=SUMPRODUCT(--(MOD(ROW(A1:A10)-ROW(A1),2)=0),--(A1:A10<""))
Biff "Arby" wrote in message ... "Biff" wrote: 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 "Arby" wrote in message ... how would you count everyother cell? Thank you Biff--another question How would I count everyother non blank cell? Thx |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Arby" wrote: Ths Max--How would you count everyother nonblank cell? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
"Biff" wrote: =SUMPRODUCT(--(MOD(ROW(A1:A10)-ROW(A1),2)=0),--(A1:A10<"")) Biff "Arby" wrote in message ... "Biff" wrote: 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 "Arby" wrote in message ... how would you count everyother cell? Thank you Biff--another question How would I count everyother non blank cell? Thx I am getting a circular refence error when i replace A1:A10 with H3:H795 which is my range--any ideas--Thx? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
"Max" wrote: 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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Arby" wrote: Ths Max--How would you count everyother nonblank cell? I am getting a circular refence error when i replace A1:A10 with H3:H795 which is my range--any ideas--Thx? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
Place the formula in a cell outside the range
- this is the normal assumption <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Arby" wrote: I am getting a circular reference error when i replace A1:A10 with H3:H795 which is my range--any ideas--Thx? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
I am getting a circular refence error when i replace A1:A10 with
H3:H795 which is my range--any ideas--Thx? Move the formula from the referenced range. In other words, don't put the formula in a cell inside the range H3:H795. Biff "Arby" wrote in message ... "Biff" wrote: =SUMPRODUCT(--(MOD(ROW(A1:A10)-ROW(A1),2)=0),--(A1:A10<"")) Biff "Arby" wrote in message ... "Biff" wrote: 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 "Arby" wrote in message ... how would you count everyother cell? Thank you Biff--another question How would I count everyother non blank cell? Thx I am getting a circular refence error when i replace A1:A10 with H3:H795 which is my range--any ideas--Thx? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
"Max" wrote: Place the formula in a cell outside the range - this is the normal assumption <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Arby" wrote: I am getting a circular reference error when i replace A1:A10 with H3:H795 which is my range--any ideas--Thx? 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
"Biff" wrote: I am getting a circular refence error when i replace A1:A10 with H3:H795 which is my range--any ideas--Thx? Move the formula from the referenced range. In other words, don't put the formula in a cell inside the range H3:H795. Biff "Arby" wrote in message ... "Biff" wrote: =SUMPRODUCT(--(MOD(ROW(A1:A10)-ROW(A1),2)=0),--(A1:A10<"")) Biff "Arby" wrote in message ... "Biff" wrote: 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 "Arby" wrote in message ... how would you count everyother cell? Thank you Biff--another question How would I count everyother non blank cell? Thx I am getting a circular refence error when i replace A1:A10 with H3:H795 which is my range--any ideas--Thx? 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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
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 "Arby" wrote in message ... "Biff" wrote: I am getting a circular refence error when i replace A1:A10 with H3:H795 which is my range--any ideas--Thx? Move the formula from the referenced range. In other words, don't put the formula in a cell inside the range H3:H795. Biff "Arby" wrote in message ... "Biff" wrote: =SUMPRODUCT(--(MOD(ROW(A1:A10)-ROW(A1),2)=0),--(A1:A10<"")) Biff "Arby" wrote in message ... "Biff" wrote: 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 "Arby" wrote in message ... how would you count everyother cell? Thank you Biff--another question How would I count everyother non blank cell? Thx I am getting a circular refence error when i replace A1:A10 with H3:H795 which is my range--any ideas--Thx? 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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
"Biff" wrote: 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 "Arby" wrote in message ... "Biff" wrote: I am getting a circular refence error when i replace A1:A10 with H3:H795 which is my range--any ideas--Thx? Move the formula from the referenced range. In other words, don't put the formula in a cell inside the range H3:H795. Biff "Arby" wrote in message ... "Biff" wrote: =SUMPRODUCT(--(MOD(ROW(A1:A10)-ROW(A1),2)=0),--(A1:A10<"")) Biff "Arby" wrote in message ... "Biff" wrote: 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 "Arby" wrote in message ... how would you count everyother cell? Thank you Biff--another question How would I count everyother non blank cell? Thx I am getting a circular refence error when i replace A1:A10 with H3:H795 which is my range--any ideas--Thx? 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 Awesome-perfect--thank you!! |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Arby" wrote: 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 |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count
You're welcome!
Biff "Arby" wrote in message ... "Biff" wrote: 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 "Arby" wrote in message ... "Biff" wrote: I am getting a circular refence error when i replace A1:A10 with H3:H795 which is my range--any ideas--Thx? Move the formula from the referenced range. In other words, don't put the formula in a cell inside the range H3:H795. Biff "Arby" wrote in message ... "Biff" wrote: =SUMPRODUCT(--(MOD(ROW(A1:A10)-ROW(A1),2)=0),--(A1:A10<"")) Biff "Arby" wrote in message ... "Biff" wrote: 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 "Arby" wrote in message ... how would you count everyother cell? Thank you Biff--another question How would I count everyother non blank cell? Thx I am getting a circular refence error when i replace A1:A10 with H3:H795 which is my range--any ideas--Thx? 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 Awesome-perfect--thank you!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need formula to count spinoffs | Excel Worksheet Functions | |||
| Subtotals by count | Excel Discussion (Misc queries) | |||
| Count consecutive dates only | Excel Discussion (Misc queries) | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Linear Mode
