SOLVED

# SUMPRODUCT OVER MULTIPLE TABLES

Copper Contributor

# SUMPRODUCT OVER MULTIPLE TABLES

Hi,

I have been trying to use some variation of the SUMPRODUCT function to sum across multiple tables (stacked on top of each other) with more than one criteria.

I wish to display sales figures as per the below:

 2023 2024 2025 PART A PART B PART C PART D

I currently have 25 tables stacked on top of each other. See below example of 3 tables:

 Apr-23 May-23 Jun-23 Jul-23 Aug-23 Sep-23 Oct-23 Nov-23 Dec-23 Jan-24 Feb-24 Mar-24 PART A \$          - \$ 100.00 \$ 150.00 \$ 264.00 \$ 489.00 \$ 255.00 \$ 164.00 \$          - \$             - \$          - \$          - \$          - PART B \$          - \$          - \$          - \$ 564.00 \$ 489.00 \$ 123.00 \$ 145.00 \$ 586.00 \$    485.00 \$          - \$          - \$          - PART C \$          - \$          - \$          - \$          - \$          - \$ 487.00 \$ 452.00 \$ 145.00 \$    165.00 \$ 321.00 \$ 598.00 \$          - PART D \$          - \$          - \$          - \$          - \$          - \$          - \$          - \$ 789.00 \$    520.00 \$ 136.00 \$ 345.00 \$ 599.00 Jul-23 Aug-23 Sep-23 Oct-23 Nov-23 Dec-23 Jan-24 Feb-24 Mar-24 Apr-24 May-24 Jun-24 PART A \$          - \$ 100.00 \$ 150.00 \$ 264.00 \$ 489.00 \$ 255.00 \$ 164.00 \$          - \$             - \$          - \$          - \$          - PART B \$          - \$          - \$          - \$ 564.00 \$ 489.00 \$ 123.00 \$ 145.00 \$ 586.00 \$    485.00 \$          - \$          - \$          - PART C \$          - \$          - \$          - \$          - \$          - \$ 487.00 \$ 452.00 \$ 145.00 \$    165.00 \$ 321.00 \$ 598.00 \$          - PART D \$          - \$          - \$          - \$          - \$          - \$          - \$          - \$ 789.00 \$    520.00 \$ 136.00 \$ 345.00 \$ 599.00 Oct-23 Nov-23 Dec-23 Jan-24 Feb-24 Mar-24 Apr-24 May-24 Jun-24 Jul-24 Aug-24 Sep-24 PART A \$          - \$ 100.00 \$ 150.00 \$ 264.00 \$ 489.00 \$ 255.00 \$ 164.00 \$          - \$             - \$          - \$          - \$          - PART B \$          - \$          - \$          - \$ 564.00 \$ 489.00 \$ 123.00 \$ 145.00 \$ 586.00 \$    485.00 \$          - \$          - \$          - PART C \$          - \$          - \$          - \$          - \$          - \$ 487.00 \$ 452.00 \$ 145.00 \$    165.00 \$ 321.00 \$ 598.00 \$          - PART D \$          - \$          - \$          - \$          - \$          - \$          - \$          - \$ 789.00 \$    520.00 \$ 136.00 \$ 345.00 \$ 599.00

The dates continue to increase as the tables go on, as the initial date of each table reflects the "start date" of a new project. Even though the tables have the same sales data, I have displayed in different tables as I can pull the "Start Date" from another sheet that governs when a new project begins.

Is this possible with a SUMPRODUCT?

Or perhaps my idea of stacking tables is a bad idea, and there is a better, more efficient way to do this?

Thanks in advance for any help.

5 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: SUMPRODUCT OVER MULTIPLE TABLES

I wouldn't stack the data. I think it's best to keep it horizontal and then use FILTER:

``=LET(filtered,FILTER(\$B2:\$AK2,RIGHT(dates,2)=RIGHT(B\$11,2),0),SUM(filtered))``

# Re: SUMPRODUCT OVER MULTIPLE TABLES

I agree with Patrick2788.

if u have more than 20 tables in one sheet need to consolidate to one sheet,try below online tool if possible.

http://e.anyoupin.cn/ceshi/jstest/pull_up_demo.php?s=consolidate_multi

# Re: SUMPRODUCT OVER MULTIPLE TABLES

@Patrick2788 Thank you for your help!

You are welcome!

# Re: SUMPRODUCT OVER MULTIPLE TABLES

Just in case, variant for vertical alignment ( see attached)

``````=LET(
selected, LET(
tData, TRANSPOSE(data),
DROP(
CHOOSECOLS(
tData,
LET(
top, TAKE(tData, 1),
f, SEQUENCE(, COLUMNS(tData)) *
((top = \$C3) + (top = "Month:")),
FILTER(f, f)
)
),
1
)
),
k, SEQUENCE(, COLUMNS(selected) / 2, , 2),
SUM(
(RIGHT(CHOOSECOLS(selected, k), 2) = RIGHT(D\$2, 2)) *
CHOOSECOLS(selected, k + 1)
)
)``````
1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: SUMPRODUCT OVER MULTIPLE TABLES

I wouldn't stack the data. I think it's best to keep it horizontal and then use FILTER:

``=LET(filtered,FILTER(\$B2:\$AK2,RIGHT(dates,2)=RIGHT(B\$11,2),0),SUM(filtered))``