PDA

View Full Version : Solved: Formula for count if between two dates



gibbo1715
10-21-2006, 08:12 AM
All

can anyone help with the following,

I have a row containing peoples age (e.g. 60), i need to add one to the total in my cell if the age is between 55 and 65, can anyone help with this please, i ve tried countif but i think that can only have one criteria?

thanks

gibbo

Jacob Hilderbrand
10-21-2006, 08:34 AM
Try this:

=SUMPRODUCT(--(A1:A5>=55),--(A1:A5<=65))

This will count exactly 55 and 65 as well. If you just want inbetween those:

=SUMPRODUCT(--(A1:A5>55),--(A1:A5<65))

Change the A1:A5 to your range. Just note that you cannot use the whole column (i.e. A:A).

Bob Phillips
10-21-2006, 08:41 AM
=COUNTIF(1:1,">=55")-COUNTIF(1:1,">65")

gibbo1715
10-21-2006, 09:14 AM
Thanks both but neither work for me, I have just realised i should have added something

I am using a formula to work out the age from a date

=IF(D5<>"",DATEDIF(D5,NOW(),"y"),"")

This is the cell I am trying to get the data from, will the fact I have a formula in there make a difference?

thanks

Gibbo

Norie
10-21-2006, 11:22 AM
gibbo

I don't see why the value being the result of a formula would cause a problem.

Can you attach a sample workbook?

gibbo1715
10-22-2006, 02:46 AM
Sorry, my mistake, now i ve revisited this it works great

thanks again

Gibbo