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

Thanks in advance.

EP


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=539707

Difficult formula SUMPRODUCT,MATCH,WEEKDAY
 

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:c t3,2)4)*(f9:ct9))*1.23


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=539707

Difficult formula SUMPRODUCT,MATCH,WEEKDAY
 

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

Thanks for the help.

EP


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=539707

Difficult formula SUMPRODUCT,MATCH,WEEKDAY
 

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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=539707

Difficult formula SUMPRODUCT,MATCH,WEEKDAY
 

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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=539707

Difficult formula SUMPRODUCT,MATCH,WEEKDAY
 

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=539707

Difficult formula SUMPRODUCT,MATCH,WEEKDAY
 

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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=539707

Difficult formula SUMPRODUCT,MATCH,WEEKDAY
 

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=539707