Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difficult Formula | Excel Worksheet Functions | |||
"Unable to set the Formula property of the Series class" with a tw | Charts and Charting in Excel | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Another difficult failed formula | Excel Worksheet Functions |