sumproduct ?

[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

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

[Ian Bartlett]

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

Thanks

"Ian Bartlett" <[email protected]> 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
>

[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

[Ian Bartlett]

SteveG

That worked a charm! Thank you very much.

Bart