Sumproduct across multiple sheets, using indirects gives #value

[Bertiebond]

Hi,
I am trying to sumproduct across multiple sheets.

e.g. cell C1 in sheet 'Total' is trying to sum C1*D1 in sheets 'A', and 'B'

I am using Excel 2010


Having seen a solution where you define the source sheets as a named range

e.g. define MS = {"A", "B"}

I have used the following formula

=SUMPRODUCT(INDIRECT(MS&"!"&CELL("address"))*INDIRECT(MS&"!"&ADDRESS(CELL("row"),CELL("col")+1)))

(also tried replacing the * with ,)

Now if I manually evaluate the formula by highlighting the indirect and hitting F9, the indirect correctly evaluates

e.g. {1, 2} and {3,4}

after which if i evalute the whole formula, it evaluates properly to 11

However

When I evaluate the formula in one go it #Values
Stepping through the formula using the 'evaluate formula' tool in excel, it hits #value at the point of the indirect
So the MS&"!"&CELL("address") becomes {"A!$C$1","B!$C$1"} but the indirect({"A!$C$1","B!$C$1"}) becomes {#value, #value} instead of the {1, 2} i get when I manually step by step evaluate

Would anyone know why this could be happening?

Thank you,
Jon

(p.s different but related, when I tried using the original full solution with a sumif involved for some reason,

(SUMPRODUCT(((SUMIF(INDIRECT(MS&"!"&CELL("address")),">"&0,INDIRECT(MS&"!"&CELL("address")))*(SUMIF(INDIRECT(MS&"!"&ADDRESS(CELL("row"),CELL("col")+1)),">"&0,INDIRECT(MS&"!"&ADDRESS(CELL("row"),CELL("col")+1))))))))

This evaluates correctly, but when used in multiple cells, e.g. c1 and c2 and c3, they all evaluate to a same value rather than each correct individual value... )

 

[barry houdini]

Hello Bertiebond, welcome to MrExcel

You probably need to use N function do "de-reference" the arrays, e.g.

=SUMPRODUCT(N(INDIRECT(MS&"!"&CELL("address",C1)))*N(INDIRECT(MS&"!"&ADDRESS(CELL("row",C1),CELL("col",C1)+1))))

Note that I also used a cell ref in the CELL functions. If you don't do that then CELL function always references the last cell you changed, hence why your SUMIF doesn't work

 

[Bertiebond]

Fantastic!
Works like a charm. Didn't know about the N function, but it does the job.
Thank you very much Barry