in a spreadsheet there is this column with dates of started membership. these date start from 2001 to 2005 different dates but no set intervals. The spread sheet in a sheet of member of a club, i need to get the amount of new member for each year in a different cell. aby help? is this even clear?!

 

like you need to sort it by year? if so Data>Sort > select "has header row" sort by that column

 

try this.....




type this
=SUM(IF(YEAR(RANGEofDATES)=YEAR,1,0))

then hit Control+Shift+Enter

example

{=SUM(IF(YEAR(B3:B20)=2004,1,0))}

the Control+Shift=Enter will add { } around the formula... dont try to manually type them in

 

very clever use of arrays :thumbup:

 

What I would do is:
Format the date column to display the date in MM/DD/YYYY format.
Then on a separate column, use the function "RIGHT" and set the value to 4 (this will strip the 4 right most numbers).

You should then have a column of just years. I would then use the function "COUNT" to get a total for each year.

 

that is a very good idea ... let's see ... brb.

 

For some reason it doesnt work! when i do that it gives me a number such as 6804. Now the date are formated in a 2004/10/05 format and tried both right and left function and it gives me
left;3680
right;6804
I dont get it! h:

 

I am guessing for this to work i would need a additionnal colum consisted of only years but in the spread sheet it is in a 2004/01/01 format and i cant really change that.

 

you dont...

the year function YEAR() extracts the year from the whole date

 

I LOVE YOU h: it worked... only thing was the language cause i am working with a french Ver.

Meyb one more question how do you use the count function? cause in a different column their is the kind of memebership. So platnum, gold, silver bronze. I would like a cell to give me amount of platnium. other cell for gold, other for.... you know what i mean.