Forum Discussion

PRESCOTTJH's avatar
PRESCOTTJH
Copper Contributor
Feb 09, 2023
Solved

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 ...
excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Feb 09, 2023

    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))
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 13, 2023

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

Resources