A calculation to count all cells ='D' where the offset cells in range have dates <= todays date

[AlanN]

I am working on a scheduling system in Excel 2002.

In cells A12: A376 I have the dates from 1/1/2004 > 12/31/2004 and in column B12:B376 the characters can be "D", "E" or "N" (day, evening, night).
I want to put a calculation in cell B8 that counts the # of "D"s in the range B12:B376 where the corresponding date in column A is <= the current date (=now()).

Can anyone help me?

TIA, AlanN

 

[Roger Govier]

Hi Alan

Try
=SUMPRODUCT(--(B12:B376="D")*(A12:A376<=Today()))

--
Regards
Roger Govier
I am working on a scheduling system in Excel 2002.

In cells A12: A376 I have the dates from 1/1/2004 > 12/31/2004 and in column B12:B376 the characters can be "D", "E" or "N" (day, evening, night).
I want to put a calculation in cell B8 that counts the # of "D"s in the range B12:B376 where the corresponding date in column A is <= the current date (=now()).

Can anyone help me?

TIA, AlanN

 

[Paul]

I am working on a scheduling system in Excel 2002.

In cells A12: A376 I have the dates from 1/1/2004 > 12/31/2004 and in
column B12:B376 the characters can be "D", "E" or "N" (day, evening, night).
I want to put a calculation in cell B8 that counts the # of "D"s in the
range B12:B376 where the corresponding date in column A is <= the current
date (=now()).

Can anyone help me?

TIA, AlanN


=SUMPRODUCT((B12:B376="D")*(A12: A376<=TODAY()))