[Formula Needed] Generating sum of specific DATEDIF results

[DJMadAdam]

Column G contains dates of birth. Column H contains a =DATEDIF formula (including "Y" Years and "YM" Months) that generates the corresponding current age.

What formula should I use to calculate the total # of cells in Column H that contain an age = or > 65, and then place that result in a cell at the bottom of Column H? Or, since Column H contains alphanumeric information (e.g. 66 years, 3 months), should I use the dates in Column G to generate this sum?

Thank you all, in advance, for your help.

~DJMA

 

[barry houdini]

Hello DJMA, welcome to MrExcel,

As you have already surmised, it's easier to calculate this using your dates of birth in column G, assuming you have dates of birth in G2:G100 try like this

=SUMPRODUCT(--(DATEDIF(G2:G100,TODAY(),"y")>=65))

although that will include any blanks as +65 too so if you might have blanks in G2:G100 amend to

=SUMPRODUCT(--(DATEDIF(G2:G100,TODAY(),"y")>=65),--(G2:G100<>""))

 

[DJMadAdam]

You're right. Some cells in the column are blank.

I will try this formula at work tomorrow. Thank you for responding so quickly to my post (it was like, what, less than a half hour?!?).

 

[DJMadAdam]

The formula worked!! I am very grateful.

Now, I should've thought of this the first time around, but how might I revise the formula to include a RANGE, e.g. everyone including and between ages 18 and 21?

Thank you, again.

 

[barry houdini]

You can amend the formula to this

=SUMPRODUCT(--(DATEDIF(G2:G100,TODAY(),"y")>=18),--(DATEDIF(G2:G100,TODAY(),"y")<=21))

Note that this is counting a four year range, i.e. all those aged 18, 19, 20 or 21

 

[DJMadAdam]

Oops! I forgot to respond that this was all terrific advice and that the formulae worked. Thank you!

~DJMA