countifs Text and Dates

M

mokihi

Hi there

I want to count how many "Level 1" cells that are older than "3/9/08". My
columns and rows look like this:

A B
Level 1 03/09/08
Level 2 04/11/07
Level 1 01/02/07
Level 1 31/03/08
Level 2 14/04/08
Level 4 16/09/06
Level 4 16/09/06

Can someone please give me the COUNTIF or SUMPRODUCT formula to calculate
this. I don't have 2007 so COUNTIFS aren't an option for me. I have read
through the other listings but can't seem to pinpoint a formula that works.

Thanks
 
T

T. Valko

Try this:

=SUMPRODUCT(--(A1:A10="level 1"),--(B1:B10>DATE(2008,9,3)))

Better to use cells to hold the criteria:

D1 = level 1
E1 = 3/9/2008

=SUMPRODUCT(--(A1:A10=D1),--(B1:B10>E1))
 
T

T. Valko

older than "3/9/08".

If "older" means before or earlier than 3/9/2008, then change to these:

=SUMPRODUCT(--(A1:A10="level 1"),--(B1:B10<DATE(2008,9,3)))

=SUMPRODUCT(--(A1:A10=D1),--(B1:B10<E1))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top