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?

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

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?

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?

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

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?

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

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?

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

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

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?