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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with multiple tab ranges
i forgot to add that in most occassions
$D$33:$D$46 some of the cells maybe empty K$33:$K$46 some of the cells maybe empty cheers |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with multiple tab ranges
Try this:
=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT(" 1:13")),"000")&"'!D33:D46"),C5,INDIRECT("'"&TEXT(R OW(INDIRECT("1:13")),"000")&"'!K33:K46"))) -- Biff Microsoft Excel MVP "Chuck" wrote in message ups.com... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with multiple tab ranges
One way
=SUMPRODUCT(SUMIF(INDIRECT("'"&{"001";"002";"003"; "004";"005";"006";"007";"008";"009";"010";"011";"0 12";"013"}&"'!$D$33:$D$46"),C5,INDIRECT("'"&{"001" ;"002";"003";"004";"005";"006";"007";"008";"009";" 010";"011";"012";"013"}&"'!$K$33:$K$46"))) if you put the lists of all the sheet names in index order somewhere on the sheet you are using for summary and call that range MySheets you can use a shorter form =SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!$D$33:$ D$46"),C5,INDIRECT("'"&MySheets&"'!$K$33:$K$46"))) it's also easier to use if you want to add sheets without editing the formula -- Regards, Peo Sjoblom "Chuck" wrote in message ups.com... 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with multiple tab ranges
And you have a reply at your other post.
Chuck wrote: 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? -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with multiple tab ranges
hi peo,
thanks for this.. i did try and apply =SUMPRODUCT(SUMIF(INDIRECT("'"&{"001";"002";"003"; "004";"005";"006";"007";"* 008";"009";"010";"011";"012";"013"}&"'!$D$33:$D $46"),C5,INDIRECT("'"&{"001";"002";"003";"004";"00 5";"006";"007";"008";"009";"010";"011";"012";"013" }&"*'! $K$33:$K$46"))) but when i do, i get a #REF error .. saying invalid cell reference. i know that $D$33:$D$46 & $K$33:$K$46 are the correct ranges for each sheet. can it be due to a cell formatting issue? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with multiple tab ranges
hi biff,
i also tried yours =SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT(" 1:13")),"000")&"'! D33:D46*"),C5,INDIRECT("'"&TEXT(ROW(INDIRECT("1:13 ")),"000")&"'! K33:K46"))) but getting the same error .. #REF .. invalid cell reference |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with multiple tab ranges
hi again biff,
=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT(" 1:13")),"000")&"'!D33:D46*"),C5,INDIRECT("'"&TEXT( ROW(INDIRECT("1:13")),"000")&"'!K33:K46"))) curious, what is the "000" for? the tab numbers? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with multiple tab ranges
i found the issue with the #REF error. seems everytime i try and paste
the code in to excel, excel has a habit of putting a "-" somewhere and i had to remove it your suggestion works great biff cheers chuck |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with multiple tab ranges
Google Groups uses the "-" as line break character. You have to be careful
when copying/pasting from Google Groups. -- Biff Microsoft Excel MVP "Chuck" wrote in message ups.com... i found the issue with the #REF error. seems everytime i try and paste the code in to excel, excel has a habit of putting a "-" somewhere and i had to remove it your suggestion works great biff cheers chuck |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with multiple tab ranges
what is the "000" for? the tab numbers?
Yes. ROW(INDIRECT("1:13")) Returns an array as the numbers: 1, 2, 3...13 Putting that inside the TEXT function we can define a number format that matches the name format of your sheets: TEXT(ROW(INDIRECT("1:13")),"000") Now that array looks like this: 001, 002, 003, ...013. And the outer INDIRECT function finishes building the whole reference: INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'! D33:D46*") This becomes: '001'!D33:D46 '002'!D33:D46 '003'!D33:D46 ..... '013'!D33:D46 -- Biff Microsoft Excel MVP "Chuck" wrote in message ups.com... hi again biff, =SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT(" 1:13")),"000")&"'!D33:D46*"),C5,INDIRECT("'"&TEXT( ROW(INDIRECT("1:13")),"000")&"'!K33:K46"))) curious, what is the "000" for? the tab numbers? |
Reply |
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) |