sumproduct ?
 

I'm trying to obtain information on how often someone works say a Saturday.
A1 Date
b1 weekday a1
d1 array of types {"E","EN","r"}
tried this returns 0
=SUMPRODUCT((C18:C382="Sat")*(D18:D382=({"E","EN", "r"})))

any help appreciated.

Bart

sumproduct ?
 

If you actually have the text string "Sat" in C18:C382, then it should
work.

Make sure there are no spaces in preceding or after any text strings in
either column C or D.

How are the dates and the weekdays being entered or populated?


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=539818

sumproduct ?
 

c18:c382 are obtained by =weekday() not text strings how would one work
around that?

Thanks

"Ian Bartlett" wrote in message
news:2mG7g.138953$WI1.77256@pd7tw2no...
I'm trying to obtain information on how often someone works say a
Saturday.
A1 Date
b1 weekday a1
d1 array of types {"E","EN","r"}
tried this returns 0
=SUMPRODUCT((C18:C382="Sat")*(D18:D382=({"E","EN", "r"})))

any help appreciated.

Bart

sumproduct ?
 

Change your formula to:

=SUMPRODUCT((C18:C382=7)*(D18:D382=({"E","EN","r"} )))

This assumes your WEEKDAY formula is written using 1 as the return type
which makes Saturday the 7th weekday. If you use a 2 as the return
type,
C18:C382 should = 6, and if you use a 3 as the return type, C18:C382
should = 5.


HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=539818

sumproduct ?
 

SteveG

That worked a charm! Thank you very much.

Bart