Sumproduct across multiple sheets

S

sh1pley

Board Regular
Joined
Dec 22, 2006
Messages
160
Hi I am using the following formula to multiple 2 cells together across many sheets:

=SUMPRODUCT(INDIRECT("'"&List&"'!D9"),INDIRECT("'"&List&"'!E9"))

List is a named range of all the sheet names

However it keeps returning "VALUE" ?

If I highlight each of the indirect formulae and press F9 it is returning the correct values to multiply but when I press enter I get "VALUE"

Any ideas?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Try

=SUMPRODUCT(N(INDIRECT("'"&List&"'!D9")),N(INDIRECT("'"&List&"'!E9")))
 
Upvote 0
AS help says, it returns a value converted to a number. Here, the indirect returns cell references, N picks out the numbers therein for SP to work on.
 
Upvote 0
Cheers

Can the formula be altered so that the cell references arent entered as text?

Be good if I could drag this formula down and across etc
 
Upvote 0
Here it comes

=SUMPRODUCT(N(INDIRECT("'"&List&"'!"&ADDRESS(ROW(A10),COLUMN(A10)+3))),N(INDIRECT("'"&List&"'!"&ADDRESS(ROW(A10),COLUMN(A10)+4))))
 
Upvote 0
That should have been A9 not A10 of course to correspond to your original question.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Sumproduct versus sum with indirect
Replies
3
Views
231
RoryA
RoryA
K
  • Question
Calculating player win-loss records
Replies
1
Views
605
KWD
K
S
  • Question
Remove simple multiplication from formulas in a selection of cells
Replies
1
Views
349
Herakles
H
Suresh Kusunuri
  • Solved
Sum amounts from various sheets
2
Replies
11
Views
736
Suresh Kusunuri
Suresh Kusunuri
M
  • Question
Countifs with Multiple criteria across multiple sheets
Replies
1
Views
645
etaf
etaf

Forum statistics

Threads
1,216,113
Messages
6,128,905
Members
449,478
Latest member
Davenil

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top