CountDates

balcardi_uk

New Member
Joined
Jan 19, 2005
Messages
4
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Try:
=SUMPRODUCT(--(B$8:$B$13<TODAY()),--(C8:$C$13="london"))
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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)
 

Forum statistics

Threads
1,147,697
Messages
5,742,688
Members
423,747
Latest member
Shadeslayers09

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top