# Difficult formula SUMPRODUCT,MATCH,WEEKDAY

1. ## Difficult formula SUMPRODUCT,MATCH,WEEKDAY

Not really sure where to start with this one. I have a forumula, =SUMPRODUCT(--(MOD(COLUMN(F9:CT9),3)=0),F9:CT9). I need to incorporate a MATCH function so that it matches the date in row f3. Similar to =IF(Date=0,"",OFFSET(\$E\$5:\$E\$103,,MATCH(Date,\$F\$3:\$CX\$3,0))).

Ultimately, I need the formula to sum every third column, based on the date in the range F3:CX3, and then multiply each column whose date is a weekday (Mon-Thu) by 1.13 and weekend (Fri-Sun) by 1.23

EP  Register To Reply

=sumproduct((mod(column(f9:ct9),3)=0)*(weekday(f3:ct3,2)<5)*(f9:ct9))*1.13 + Sumproduct((mod(column(f9:ct9),3)=0)*(weekday(f3:ct3,2)>4)*(f9:ct9))*1.23  Register To Reply

3. ## Looks good, but...

It looks like it would work, but its giving me an answer of zero.

Thanks for the help.

EP  Register To Reply

4. it worked for me, you do have the dates (non text) in row 3 and data (numbers) in row 9 right?  Register To Reply

5. ## Got it!

All of the cell references were to another worksheet. Once I included the reference to the other sheet Data!, it worked like a charm.

Many Thanks.

EP  Register To Reply

6. You could simplify it somewhat

=SUMPRODUCT(--(MOD(COLUMN(F9:CT9),3)=0),F9:CT9,LOOKUP(WEEKDAY(F3:CT3,2),{0,1.13;5,1.23}))  Register To Reply

7. ## Perfect, but can I...

Can I add a cell reference for the 1.13 and 1.23: Data!DC6, and Data!DC7 respectively.

I tried and its not working.

Thanks again.

EP  Register To Reply

8. You can do that this way

=SUMPRODUCT(--(MOD(COLUMN(F9:CT9),3)=0),F9:CT9,LOOKUP(WEEKDAY(F3:CT3,2),{0;5},data!DC6:DC7))  Register To Reply