SUMIF with multiple criteria

[Guest]

Two Questions:
If Y$29:IV$29 are dates, "&$C$5 and "&$C$6 are the start and end dates of a
unit, $Y33:$IV33 are hours to be totaled, why doesn't the second formula work
the same as the first?

=SUMIF(Y$29:IV$29,"<="&$C$6,$Y34:$IV34)-SUMIF(Y$29:IV$29,"<"&$C$5,$Y33:$IV33)

=SUMIF(Y$29:IV$29,AND(">="&$C$5,"<="&$C$6),$Y33:$IV33)

I now want the formula to also only add the hours if the date falls on a
Monday or Wednesday in the unit. How can I do this?

Thanks,
Stacy

 

[JE McGimpsey]

The second formula doesn't work because SUMIF() requires a single number
expression as the criterion.

Try:
=SUMPRODUCT(--(Y$29:IV$29>=$C$5),--(Y$29:IV$29<=$C$6),Y33:IV33)

(Note: I'm not sure why your comparison row is absolute row, relative
column, and your value row is absolute column, relative row).

=SUMPRODUCT(--(Y$29:IV$29>=$C$5),--(Y$29:IV$29<=$C$6),
((WEEKDAY(Y$29:IV$29)=1) + (WEEKDAY(Y$29:IV$29)=3)),Y33:IV33)

 

[Guest]

JE - shouldn't your WEEKDAY() functions either test for 2 & 4 or specify a 1
as the second argument? i.e.,

WEEKDAY(Y$29:IV$29,1)=1

 

[Guest]

Make that a 2 for the second argument

JE - shouldn't your WEEKDAY() functions either test for 2 & 4 or specify a 1
as the second argument? i.e.,

WEEKDAY(Y$29:IV$29,1)=1

 

[JE McGimpsey]

Yup, I'm a day ahead of myself - thanks for the correction.