COUNTIF

[Guest]

I got a worksheet for each month of the year with a list of 300 names in
column "C" and 300 ages in column "D" from 18 year old to 99 year old.
I got a resut worksheet that will tell me how many 18,19,20...99 year old
were contact in January, February...December.
This is my formula :
=COUNTIF(JANUARY!$D$2:$D$300,"18")
Works perfect.
Problem :
I have to copy/paste every month from 18 to 99 changing the age number each
time.
Question :
Is there a way to do a consecutive pasting? or another way that I can use
the same formula just changing the age? "18", "19"... without doing it
manually?
Is time consuming for 2007. Imagine for 2006,2005,2004...Thanks a lot.

 

[Guest]

=COUNTIF(JANUARY!$D$2:$D$300,"18")

Replace the above with this, then copy down as far as required:
=COUNTIF(JANUARY!$D$2:$D$300,ROWS($1:1)+17)

In the start cell, ROWS($1:1)+17 evaluates to 18.
When you copy the formula down, it'll increment to: 19, 20, etc as required
in successive rows

 

[Guest]

Thanks Max. Works very well. Appreciatte your time.

 

[Ragdyer]

Why not just *start* with
Rows($1:18)
?<g>

 

[Max]

Why not just *start* with
Rows($1:18)
?<g>

Perhaps my thoughts were to make it a little more
"insert-row" proof and "delete-row" proof ? <g>

---

 

[Ragdyer]

What if you delete Row1?
<g>

 

[Max]

Except row1, of course <g>