sumproduct & multiple tab ranges

[Chuck]

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?

 

[Chuck]

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

 

[Dave Peterson]

Your formula worked ok for me. Is there a chance that you don't have numbers in
the one range where you multiplied ('001'!k33:K46?
--('013'!$D$33:$D$46=C5)*('001'!$K$33:$K$46)

Do you have any errors in any of the ranges?

Is there a reason you multiplied instead of just using something like:

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

 

[RagDyer]

To calculate across sheets, you can make a list of your sheet names, in an
out of the way location, and assign a name to that list, and then use that
name in your formula.

Since your sheets sheets are in numerical order, making this list is easy.
Say you format Z1 to Text, then enter:
001
Then drag down to increment to
013
in Row13.

Make sure the list matches *exactly* with the names on your sheet tabs.

Select Z1 to Z13, click in the name box (left of the formula bar), and type
something short, like
list
Then hit <Enter>

Now, try this formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!D3346"),C5,INDIRECT("'"&list&"'!K33:K46")))