Summing cell values from multiple sheets where both row and column criteria qualifies

[sunnyk]

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!!

 

[ping_pong]

I'm going to start looking at this and trying to understand your question. If you could upload a sample workbook showing what you want, someone will likely be able to find the answer to your question more quickly.

 

[ping_pong]

So your table contains range address like "Sheet32!B5:B55"? Stored as text?

trying to fetch sum of values in multiple sheets

In general your formula will refer to different sheets? Or within one cell you need to sum values from different sheets?

Here's a starting point. Tell me what's right or wrong...

=SUM(
    INDIRECT(
        INDEX(
            Table1[#All],
            MATCH(
                "ABC",
                Table1[Col1],
                0
            ),
            MATCH(
                "ABC",
                Table1[#Headers],
                0
            )
        )
    )
)

 

[sunnyk]

Thanks ping_pong! The following example demonstrates the problem. Let me know if you need further information. Thanks so much for your help!!

[TABLE="class: grid, width: 525"]
<tbody>[TR]
[TD]Summary Table[/TD]
[TD]STU[/TD]
[TD]TUV[/TD]
[TD]XYZ[/TD]
[TD]ZZZ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Table on left summarizes total for each row/column combination

This is where I want to use formula to sumproduct values for all row/column combinations from multiple tabs in the workbook.[/TD]
[/TR]
[TR]
[TD]AAB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HIJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KLM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MMM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Table 1 for sheet names (used in Indirect clause to dynamically reference the sheets)
[TABLE="class: grid, width: 161"]
<tbody>[TR]
[TD]SheetNames[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Data in Sheet1 (A1:E6)
[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD][/TD]
[TD]STU[/TD]
[TD]TUV[/TD]
[TD]XYZ[/TD]
[TD]ZZZ[/TD]
[/TR]
[TR]
[TD]ABB[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]11[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD]12[/TD]
[TD]11[/TD]
[TD]4[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]HIJ[/TD]
[TD]9[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

Data in Sheet2 (A1:D7)
[TABLE="class: grid, width: 256"]
<tbody>[TR]
[TD][/TD]
[TD]STU[/TD]
[TD]XYZ[/TD]
[TD]ZZZ[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]4[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]HIJ[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]KLM[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]MMM[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]

Data in Sheet3 (A1:D6)
[TABLE="class: grid, width: 256"]
<tbody>[TR]
[TD][/TD]
[TD]TUV[/TD]
[TD]XYZ[/TD]
[TD]ZZZ[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]HIJ[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]KLM[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]MMM[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Data in Sheet4 (A1:C4)
[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD][/TD]
[TD]STU[/TD]
[TD]TUV[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]7[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]HIJ[/TD]
[TD]11[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(T(INDIRECT("'"&Table1[SheetNames]&"'!A2:A10"))="ABC",IF(T(INDIRECT("'"&Table1[SheetNames]&"'!B1:E1"))="XYZ",N(INDIRECT("'"&Table1[SheetNames]&"'!B2:E10")))))

 

[sunnyk]

Thank you so much Aladin! I tried your formula in the summary table. But, it is fetching values only where the combination (example ABC and XYZ) being compared matches the first row and first column in any of the sheets. And, so I am getting 0 for ABC and XYZ combination.

Following is what the Summary table looks like with the formula.


[TABLE="class: grid, width: 369"]
<tbody>[TR]
[TD]Summary Table[/TD]
[TD]STU[/TD]
[TD]TUV[/TD]
[TD]XYZ[/TD]
[TD]ZZZ[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]AAB[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]ABB[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD]0[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]HIJ[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]KLM[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]MMM[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Upon checking Evaluate formula, it looks like the array range in sheets is not being expanded to compare all the cells in range. Only the first row/column combination in sheets is being compared.

Control+shift+enter, not just enter:

=SUM(IF(T(INDIRECT("'"&Table1[SheetNames]&"'!A2:A10"))="ABC",IF(T(INDIRECT("'"&Table1[SheetNames]&"'!B1:E1"))="XYZ",N(INDIRECT("'"&Table1[SheetNames]&"'!B2:E10")))))

 

[pgc01]

Hi sunnyk
Welcome to the board

Looking at your formulas it seems to me that it's not possible to do it this way as it's like you want to work directly with a 3D array, which excel does not support.

I see 2 options:

- use vba, it would not be difficult to loop through the worksheets and do the calculations

- it case you really need (or want) to do it with a formula, I believe that you'd have to flatten the 3D array into a 2D array to perform the calculations.
It would certainly be a big formula, but I remember doing it once and it is maneageble using some named formulas.

Of course I can be wrong and I'll be happy if there's a simple direct solution.

 

[Aladin Akyurek]

Thank you so much Aladin! I tried your formula in the summary table. But, it is fetching values only where the combination (example ABC and XYZ) being compared matches the first row and first column in any of the sheets. And, so I am getting 0 for ABC and XYZ combination.

[...]

Upon checking Evaluate formula, it looks like the array range in sheets is not being expanded to compare all the cells in range. Only the first row/column combination in sheets is being compared.

Right, quite so. I agree with what pgc01 asserts in his reply.

 

[sunnyk]

Thanks so much PGC and Aladin for looking into my question!!

Given that (fortunately) I don't have to deal with too many tabs to deal with, I will use a formula to sum the 3d formulas for each sheet.

What I found strange was that that out of the two formulas in my first post: one expanded the entire cell range (but only for the first sheet) for comparison using index and match, and another looked into each of the sheets but compared only the first row/column combination (did not expand cell range). This led me to believe that there was some way Excel would allow me to reap both the benefits (looking into each of the sheets and across entire cell range for full comparison).

Just a follow-up question: For the 1st formula (using Sumproduct, Index, Indirect, Match) I did not have to use Ctrl+Shift+Enter for it to work. However, for the 2nd formula (using Sumproduct, Indirect) I had to use Ctrl+Shift+Enter for it to work. Why is former being treated as regular formula but latter being treated as Array formula?

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))),)

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")))

Thanks,
Sunny