sumproduct ?

I

Ian Bartlett

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
 
V

Vito

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

Ian Bartlett

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

Thanks
 
S

SteveG

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
 
Top