sumproduct & 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)


however, everytime i use the above, i get #VALUE eror.

can anyone advise if the above will work?


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


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?

Do you have any errors in any of the ranges?

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



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:
Then drag down to increment to
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
Then hit <Enter>

Now, try this formula:


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question