CountDates

[balcardi_uk]

hi

this is my sample table

A = name B = dob C = location
jane 01/01/2005 london
terry 01/01/2005 cardiff
david 02/01/2005 london
tracey 01/01/2005 london
harry 19/01/2005 london
mike 19/01/2005 swansea


=COUNTIF(B4:B100,"<" & TODAY())
The above formula counts how many in babies were born before current day.
But i need to know how many babies were born before current day AND were born in london. The letters next to headings denote column letters. I hope someone can help!!!

cheers

Balcardi

 

[tactps]

Try:
=SUMPRODUCT(--(B$8:$B$13<TODAY()),--(C8:$C$13="london"))

 

[MycroftII]

Type

=SUM((B4:B10 < TODAY())*(C4:C100="london"))


and press CTRL+SHIFT+Enter

 

[tactps]

Sorry, post is worng (don't know what happened):
=SUMPRODUCT(--(B$8:$B$13<today()),--($C$8:$C$13="london"))

Adjust the ranges to suit, but make sure they are the same length (ie all of mine are from row 8 to row 13)

 

[Aladin Akyurek]

=SUMPRODUCT(--(DOBrange < TODAY()),--(LocationRange = "London"))

 

[balcardi_uk]

Thanks everyone u've all been excellent, I've got it sorted
Proper BO!!!