Forum Discussion
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.
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))
5 Replies
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) ) )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_multiI 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))Patrick2788 Thank you for your help!
- You are welcome!
