countifs Text and Dates

[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

 

[Max]

Assuming real dates in col B
One way, in say, C1:
=SUMPRODUCT((A1:A7="Level 1")*(B1:B7< --"3 Sep 2008"))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik

 

[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))

 

[Max]

I interp'd OP's spec: > .. older than "3/9/08"
as meaning: dates earlier than or before "3/9/08"
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700, Files:359, Subscribers:55
xdemechanik
---

 

[T. Valko]

Yeah, you're probably right.

 

[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))

 

[mokihi]

Thanks so much - you guys are magic - my headache has finally gone!

 

[Max]

Welcome. Take a moment to press the "Yes" buttons (like the ones below) in
the 2 responses which answered your query
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700, Files:359, Subscribers:55
xdemechanik