Using COUNTIF to find number of accounts in an age group

[Leah T.]

Hello,

I have a spreadsheet listing the dates (cells e4:e37) that our accounts opened. I would like to know how many of those accounts are more than 10 years old, and how many are 10 years old or younger. This needs to be based on today's date (not a static date). I do not need to know the "age" of the account, just the total number in each age group.

I tried to use the COUNTIF function, but kept getting an error or "0" (which is not correct).

I was working on variations of this idea:

=COUNTIF(e4:e37,"<= 10 years ago")

but haven't been able to make it work.

Am I misusing COUNTIF? Is there another way to calculate the number of accounts in each age group?

Thanks,

Leah T.

 

[Oaktree]

Welcome to the board.

Assuming E4:E37 store Excel dates, try:

=COUNTIF(E4:E37,"<="&DATE(YEAR(TODAY())-10,MONTH(TODAY()),DAY(TODAY())))

 

[Jonmo1]

Welcome to the board, try this

=COUNTIF(E4:E37,"<="&DATE(YEAR(TODAY())-10,MONTH(TODAY()),DAY(TODAY())))

 

[Jonmo1]

or a shorter formula confirmed with CTRL + SHIFT + ENTER

=SUM(IF(DATEDIF(A1:A10,TODAY(),"y")>=10,1,0))

after pasting formula, highlight cell press F2, then press
CTRL + SHIFT + ENTER

This puts brackets {} around the formula.

 

[Leah T.]

Aha! The COUNTIF formula worked! My brain was beginning to hurt trying to figure out where my logic was failing.

Thank you!