performing a function on a variable cell

[itfellow]

Hi all,

I've got a spreadsheet with one column that is full of dates, and I want to add up the number of dates that fall within each year. So, if, for example, cells A1:A10 each have dates, I would like to know if it is possible to have a formula in cell A11 which counts all of the dates that fall in year 2008 by using:

=countif(a1:a10, year(?)=2008)

where ? is a variable which refers to the current cell being examined. Is there a way to do this in Excel?

 

[SkipVought]

Hi,
[tt]
=sumproduct(--(a1:a10>=DateValue("2008/01/01"))*(a1:a10<=DateValue("2008/12/31")))
[/tt]


Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[Chopstik]

Not sure of a direct solution to your problem, but an alternative workaround would be to insert a column next to your date column. Insert the year value of the preceding column using

=year(colA)

From there, you could then do a countif on the inserted column since you would then be looking only at the year as you designated in your post.

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[SkipVought]

also
[tt]
=SUMPRODUCT(--(YEAR(A1:A10)=2008))
[/tt]

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[yogia]

Or ...

=COUNTIF(A1:A10,">12/31/2007")-COUNTIF(A1:A10,">12/31/2008")

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[itfellow]

Thanks, the SUMPRODUCT formula did the trick perfectly.