Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with multiple tab ranges
hi all,
just curious if the following can be possible with SUMPRODUCT i have designed a budget tracking sheet which calculates PO values & account codes. right now, the sheet is using a pivot table to look through all po tabs and provide a summary of account codes & associated costs. unfortunately i have to always udpate the pivot table once i modify a value in one of the PO tabs. i am hoping to remove this pivot table and maybe use sumproduct (or whatever that will work) to remove this dependancy.. so, i have a standard sumproduct calculation as seen below $D$33:$D$46 = account code C5 = what the account code should be K$33:$K$46 = cost of the item (which should have that account code assigned to it) =SUMPRODUCT(--('001'!$D$33:$D$46=C5),--('002'!$D$33:$D$46=C5),--('003'! $D$33:$D$46=C5),--('004'!$D$33:$D$46=C5),--('005'!$D$33:$D$46=C5),-- ('006'!$D$33:$D$46=C5),--('007'!$D$33:$D$46=C5),--('008'!$D$33:$D $46=C5),--('009'!$D$33:$D$46=C5),--('010'!$D$33:$D$46=C5),--('011'!$D $33:$D$46=C5),--('012'!$D$33:$D$46=C5),--('013'!$D$33:$D$46=C5)*('001'! $K$33:$K$46),--('002'!$K$33:$K$46),--('003'!$K$33:$K$46),--('004'!$K $33:$K$46),--('005'!$K$33:$K$46),--('006'!$K$33:$K$46),--('007'!$K $33:$K$46),--('008'!$K$33:$K$46),--('009'!$K$33:$K$46),--('010'!$K $33:$K$46),--('012'!$K$33:$K$46),--('013'!$K$33:$K$46)) however, everytime i use the above, i get #VALUE eror. can anyone advise if the above will work? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct using named ranges and multiple criteria | Excel Discussion (Misc queries) | |||
Sumproduct and Ranges | Excel Discussion (Misc queries) | |||
Multiple dynamic ranges in a Sumproduct | Excel Worksheet Functions | |||
Multiple dynamic ranges in a Sumproduct | Excel Worksheet Functions | |||
sumproduct between 2 ranges | Excel Discussion (Misc queries) |