Forum Discussion

ravanwijk's avatar
ravanwijk
Copper Contributor
Nov 30, 2021
Solved

SUMIFS with OR Logic and spilling results

Hi, I am looking for a way to design a formula that can fill a table by spilling the result. The formula is composed of a sum that requires to meet certain conditions (Logic AND + Logic OR). Once I ...
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 30, 2021

    ravanwijk 

    I tried to combine all in one spill

    with

    =IFERROR(
    LET(
        select, FILTER(B16#, $B$17:$D$17 <> ""),
        c, COLUMNS(select),
        fSel, MMULT(--(select = dB[Forecast]), SEQUENCE(c, , 1, 0)) * dB[Incl],
        uMonths, SORT(UNIQUE(FILTER(dB[Month], fSel))),
        uRegions, TRANSPOSE(SORT(UNIQUE(FILTER(dB[Region], fSel)))),
        nRegions, COLUMNS(uRegions),
        nMonths, ROWS(uMonths),

        data, MMULT(
            --(uMonths = TRANSPOSE(dB[Month])),
            (uRegions = dB[Region]) * dB[Value]
        ),
        total, MMULT(data, SEQUENCE(nRegions, , 1, 0)),

        k, SEQUENCE(nMonths + 1, nRegions + 2),
        kR, INT((k - 1) / (nRegions + 1 + 1)) + 1,
        kC, MOD(k - 1, nRegions + 1 + 1) + 1,
        IF(
            kR = 1,
            IF(
                kC = 1,
                "R/M",
                IF(kC = nRegions + 2, "Total", INDEX(uRegions, kC - 1))
            ),
            IF(
                kC = 1,
                INDEX(uMonths, kR - 1),
                IF(
                    kC = nRegions + 2,
                    INDEX(total, kR - 1),
                    INDEX(data, kR - 1, kC - 1)
                )
            )
        )
    ),
    "no data"
)
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Nov 30, 2021

ravanwijk 

=SUMPRODUCT(((dB[Forecast]=Table!$C$2)+(dB[Forecast]=Table!$C$3)+(dB[Forecast]=Table!$C$4))*(dB[Month]=Table!$B25)*(dB[Region]=Table!C$24)*dB[Value])

 

Is this what you are looking for?

ravanwijk's avatar
ravanwijk
Copper Contributor
Nov 30, 2021

OliverScheurich thanks for your reply. Yes, this works. I have even worked out such formula myself based on SUMIFS in array formula:

={SUM(SUMIFS(dB[Value],dB[Month],$B9,dB[Region],C$8,dB[Forecast],$C$2:$C$4))}

 

However, instead of using this approach by populating every cell, I was wondering whether there is a way to use the spill functionality. I.e. only type 1 formula and then the whole table is spilled with the results.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 30, 2021

    ravanwijk Perhaps like in the attached file. But you'll need to expand the data table to test for either of the three Forecast values (changed one of then just to test). Now the formula spills as desired. Though, I'm wondering why you wouldn't use a pivot table. Included one to demonstrate what I mean.

Resources