countifs Text and Dates
 

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

countifs Text and Dates
 

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
---
"mokihi" wrote:
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

countifs Text and Dates
 

Try this:

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

Better to use cells to hold the criteria:

D1 = level 1
E1 = 3/9/2008

=SUMPRODUCT(--(A1:A10=D1),--(B1:B10E1))

--
Biff
Microsoft Excel MVP


"mokihi" wrote in message
...
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

countifs Text and Dates
 

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

countifs Text and Dates
 

Yeah, you're probably right.

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
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
---

countifs Text and Dates
 

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


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

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

Better to use cells to hold the criteria:

D1 = level 1
E1 = 3/9/2008

=SUMPRODUCT(--(A1:A10=D1),--(B1:B10E1))

--
Biff
Microsoft Excel MVP


"mokihi" wrote in message
...
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

Thanks
 

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

"mokihi" wrote:

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

Thanks
 

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
---
"mokihi" wrote in message
...
Thanks so much - you guys are magic - my headache has finally gone!