# sumproduct & multiple tab ranges

C

#### 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?

C

#### 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

D

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

R

#### 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

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