expanding on sumproduct command to add all cases

[AlanN]

Thanks to Roger Govier and Paul for previous help! This is a restart of an
older thread...


I want to expand on the formula below to add together all the instances of
"D", "E", and "N" in the range B12:B376, can anyone offer help with
doctoring the formula
=SUMPRODUCT(--(B12:B376="D")*(A12:A376<=Today())) to do this???

See below for original thread

Thanks again, Alan

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

PART 2

Hi Alan

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

 

[Frank Kabel]

Hi Alan
try
=SUMPRODUCT(--(B12:B376={"D","E","N"})*(A12:A376<=Today()))

HTH

 

[Bob Phillips]

Frank,

The -- is superfluous in this formula.

See you are delimiting the array values with comma<vbg>

Bob

 

[Frank Kabel]

Frank,

The -- is superfluous in this formula.

you're right, but I have an excuse - just copied from the OP

See you are delimiting the array values with comma<vbg>

lol

Frank
P.S.. I currently have another one of this regional trasnlation
problems: Entering a list for data validation consisting of decimal
values via VBA -> won't work if you have the ',' as decimal delimiter
(and not the '.')

 

[AlanN]

I have tried your formula, but it only seems to work referencing the first
12 rows of the range... any ideas?

Thanks, Alan

 

[Frank Kabel]

Hi Alan
what do you mean with 'does only work for the first 12 rows'. This
formula should work o.k. for all rows. Some ideas you might check:
- are there spaces or error codes within your range
- it will only look for single characters within your range

maybe you can post some exmaple data (plain text please - no
attachment) for your problem
Frank

 

[AlanN]

Here is a text sample of my test...

=SUMPRODUCT((B5:B42={"D","E","N"})*(A5:A42<=TODAY())) Text of
formula in cell B2
10 << Total Worked units

DATE SHIFT TYPE
1/19/2004 D D= Days
1/20/2004 X X= Off
1/21/2004 D E= Evenings
1/22/2004 D N= Nights
1/23/2004 X
1/24/2004 D
1/25/2004 D
1/26/2004 E
1/27/2004 D
1/28/2004 N
1/29/2004 D
1/30/2004 E
1/31/2004 N
2/1/2004 E
2/2/2004 N
2/3/2004 E
2/4/2004 E
2/5/2004 E
2/6/2004
2/7/2004
2/8/2004
2/9/2004
2/10/2004
2/11/2004
2/12/2004
2/13/2004
2/14/2004
2/15/2004
2/16/2004
2/17/2004
2/18/2004
2/19/2004
2/20/2004
2/21/2004
2/22/2004
2/23/2004
2/24/2004
2/25/2004

 

[AlanN]

I just smacked myself... I am switching between counting days BEFORE todays
date and after that date separately- I also need to count PLANNED dates.

Many thanks, Alan

 

[Bob Phillips]

Alan,

10 is correct, so what's the problem.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi Alan
if I count this on my own, 10 is the correct answer, so there's the
problem?. Just keep in mind, you're only searching for entries
older/equal than today. So the last six entries are not counted
(depending on your time-zone, of course -> today = 2004/1/30 23:30 here
in Germany)
Frank