Difficult formula SUMPRODUCT,MATCH,WEEKDAY

[edwardpestian]

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

Thanks in advance.

EP

 

[duane]

How About This?

=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

 

[edwardpestian]

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

Thanks for the help.

EP

 

[duane]

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

 

[edwardpestian]

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

 

[daddylonglegs]

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

 

[edwardpestian]

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

 

[daddylonglegs]

You can do that this way

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