SUMPRODUCT OVER MULTIPLE TABLES

[PRESCOTTJH]

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.

[#2 · Accepted solution]

@PRESCOTTJH 

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

[#3]

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

 

[#4]

@Patrick2788 Thank you for your help!

[#5]

You are welcome!

[#6]

@PRESCOTTJH 

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