Countif for 2 criteria: if = Y & before set date

[joanna_fisher]

Hi.

Please help - I'm really a bit bamboozled about this one and have tried many different formulas but with no success!

I've got a waiting list database and need to do monthly figures; I need to find out how many people are on the waiting list and how many have been waiting longer than so many weeks.

So, I want to take all the ones in column U that are Y (Yes for on waiting list) and where the date in column H is before 25/11/10 (which will be 18 weeks on 31/03/11). The problem is, not all my dates in column H are in the same format as people have entered them different over the years. I have changed all the dates that are Y in column U to the format 28/03/11 so I'm hoping it will only count these in the formula.

Preferably, I will be displaying this data on a new sheet called 'Monthly Stats' and the sheet I'm taking it from is called 'All referrals'. I have done the first part of this formula as =COUNTIF('All referrals'!U:U,"Y") and this gives me the total number of people on the waiting list, it's just the date bit!

Also, just to be clear, there are nearly 1000 entries on the database from which I am trying to extract the relevant ones!

Thank you so much in anticipation!

Jo.
Kent, England.

 

[barry houdini]

Hello Jo, welcome to MrExcel

Which version of Excel are you using? With Excel 2007 and later you can use COUNTIFS (with an "S") to count with multiple conditions, i.e. for your specific problem

=COUNTIFS('All referrals'!U:U,"Y",'All referrals'!H:H,"<"&DATE(2010,11,25))

or you can put the date in a cell, e.g. B2 and use just

=COUNTIFS('All referrals'!U:U,"Y",'All referrals'!H:H,"<"&B2)

 

[joanna_fisher]

Hello Jo, welcome to MrExcel

Which version of Excel are you using? With Excel 2007 and later you can use COUNTIFS (with an "S") to count with multiple conditions, i.e. for your specific problem

=COUNTIFS('All referrals'!U:U,"Y",'All referrals'!H:H,"<"&DATE(2010,11,25))

or you can put the date in a cell, e.g. B2 and use just

=COUNTIFS('All referrals'!U:U,"Y",'All referrals'!H:H,"<"&B2)

Thank you so much for speedy reply! I'm using Excel 2003 so guessing I cannot do the above?

 

[barry houdini]

OK in 2003 try SUMPRODUCT....but you need to use a fixed range rather than the whole column, e.g.

=SUMPRODUCT(('All referrals'!U$1:U$2000="Y")*('All referrals'!H$1:H$2000< DATE(2010,11,25)))

 

[joanna_fisher]

FANTASTIC - it works! It has given me an answer of 96 which sounds about right! No more mind-boggling mornings trying to work this out or counting through files!

I had tried the SUMPRODUCT way before but was obviously unsuccessful because I didn't use a fixed range. Also, I didn't know that the way you have entered the date into the formula is how it must be done.

Thank you so SO much!

All the best,

Jo.