# 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

=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

3. ## Looks good, but...

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

Thanks for the help.

EP

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

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

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}))

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

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))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1