Hi MrExcel community,
I will highly appreciate any help with the following.
I am trying to fetch sum of values in multiple sheets where a row header match and a column header match criteria holds true (similar to a matrix, one each across multiple tabs).
I have defined the Sheet names in a table Table1 (Sheet1, Sheet2, Sheet3, etc.).
I want to sum the values in each of these sheets where row header (column A) contains ABC and column header (row 1) contains XYZ.
I tried the following 2 formulas but none worked:
Formula 1:
=IFERROR(SUMPRODUCT(INDEX(INDIRECT("'"&Table1[SheetNames]&"'!$B$2:$E$10"),MATCH(“ABC”,INDIRECT("'"& Table1[SheetNames]&"'!$A$2:$A$10"),0),MATCH(“XYZ”,INDIRECT("'"& Table1[SheetNames]&"'!$B$1:$E$1"),0))),)
Result: This formula when entered as an array formula (Ctrl+Shift+Enter) only looks at the Sheet1 (Array within Indirect doesn’t expand)
Formula 2:
=SUMPRODUCT((T(INDIRECT("'"& Table1[SheetNames]&"'!$A$2:A10"))=”ABC”)*(T(INDIRECT("'"& Table1[SheetNames]&"'!$B$1:$E$1"))=”XYZ”),N(INDIRECT("'"& Table1[SheetNames]&"'!$B$2:$E$10")))
Result: This formula when entered only look for the first cell in the cell range to compare (i.e.; only references A2 within each sheet for ABC, and only references B1 within each sheet for XYZ) – rest of the cells in array are not compared at all
Thanks a Ton in advance!!!
PS: I ended up posting this question in 3 related threads - kindly ignore those duplicate posts. As recommended by Mark858, I am creating this new thread and have requested deletion for those duplicate posts in other threads. Thanks!!
I will highly appreciate any help with the following.
I am trying to fetch sum of values in multiple sheets where a row header match and a column header match criteria holds true (similar to a matrix, one each across multiple tabs).
I have defined the Sheet names in a table Table1 (Sheet1, Sheet2, Sheet3, etc.).
I want to sum the values in each of these sheets where row header (column A) contains ABC and column header (row 1) contains XYZ.
I tried the following 2 formulas but none worked:
Formula 1:
=IFERROR(SUMPRODUCT(INDEX(INDIRECT("'"&Table1[SheetNames]&"'!$B$2:$E$10"),MATCH(“ABC”,INDIRECT("'"& Table1[SheetNames]&"'!$A$2:$A$10"),0),MATCH(“XYZ”,INDIRECT("'"& Table1[SheetNames]&"'!$B$1:$E$1"),0))),)
Result: This formula when entered as an array formula (Ctrl+Shift+Enter) only looks at the Sheet1 (Array within Indirect doesn’t expand)
Formula 2:
=SUMPRODUCT((T(INDIRECT("'"& Table1[SheetNames]&"'!$A$2:A10"))=”ABC”)*(T(INDIRECT("'"& Table1[SheetNames]&"'!$B$1:$E$1"))=”XYZ”),N(INDIRECT("'"& Table1[SheetNames]&"'!$B$2:$E$10")))
Result: This formula when entered only look for the first cell in the cell range to compare (i.e.; only references A2 within each sheet for ABC, and only references B1 within each sheet for XYZ) – rest of the cells in array are not compared at all
Thanks a Ton in advance!!!
PS: I ended up posting this question in 3 related threads - kindly ignore those duplicate posts. As recommended by Mark858, I am creating this new thread and have requested deletion for those duplicate posts in other threads. Thanks!!