Bertiebond
New Member
- Joined
- Mar 13, 2014
- Messages
- 2
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... )
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... )
Last edited: