Exce SUMPRODUCT help

[inkserious]

I have a SUMPRODUCT formula that is working fine. I need to add another array and I can't seem to get it to work. I'm trying to sum the columns limited to a specific day of the week. For example, I need to sum all Saturdays in the range. The following formula is a working example without the WEEKDAY function.

=SUMPRODUCT(--(MOD(COLUMN(F5:CR5),3)=0),--(F3:CR3<=CY2),F5:CR5)

F5:CR5 is the data range. F3:CR3 is the date range and CY2 is a date - for example 5/22/2010. So within the date range F3:CR3, I need to sum all Saturdays less than or equal to 5/22/2010.

I tried adding a WEEKDAY function as an additional array, but I can't seem to get it to work.

=SUMPRODUCT(--(MOD(COLUMN(F5:CR5),3)=0),--(WEEKDAY(F3:CR3,2)=5),--(F3:CR3<=CY2),F5:CR5)

Any help would be greatly appreciated - I'm losing sleep over this one.

Regards,

-ep

 

[SkipVought]

Hi,
[tt]
=SUMPRODUCT(--(MOD(COLUMN(F5:CR5),3)=0),--(TEXT(F3:CR3,"ddd")="Sat"),--(F3:CR3<=CY2),F5:CR5)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

WEEKDAY works fine, but if the second argument is 2, Saturday is returns 6.

combo

 

[inkserious]

Got it! Since I didn't have any data in the Friday columns and I had the WEEKDAY serial number set to Friday, the formula was returning zero. Once you pointed out that I was inadvertantly summing Fridays instead of Saturdays, changing 5 to 6 in the WEEKDAY function did the trick. .

Thanks for the help.