|
SUMIF with multiple criteria
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:$I V33) 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 |
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) In article , "stacyjhaskins" wrote: 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:$I V33) 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 - 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" wrote: 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) In article , "stacyjhaskins" wrote: 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:$I V33) 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 |
Make that a 2 for the second argument
"Duke Carey" wrote: 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" wrote: 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) In article , "stacyjhaskins" wrote: 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:$I V33) 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 |
Yup, I'm a day ahead of myself - thanks for the correction.
In article , "Duke Carey" wrote: JE - shouldn't your WEEKDAY() functions either test for 2 & 4 or specify a 1 as the second argument? i.e., |
| All times are GMT +1. The time now is 10:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com