SOLVED

SUMPRODUCT OVER MULTIPLE TABLES

Copper Contributor

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:

 

 202320242025
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-23May-23Jun-23Jul-23Aug-23Sep-23Oct-23Nov-23Dec-23Jan-24Feb-24Mar-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-23Aug-23Sep-23Oct-23Nov-23Dec-23Jan-24Feb-24Mar-24Apr-24May-24Jun-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-23Nov-23Dec-23Jan-24Feb-24Mar-24Apr-24May-24Jun-24Jul-24Aug-24Sep-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 Hans Vogelaar (MVP)
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))

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

 

Screenshot_2023-02-11-09-06-29-388_cn.uujian.browser.jpg

@Patrick2788 Thank you for your help!

You are welcome!

@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)
    )
)
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
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))

View solution in original post