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"
)
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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"
)
  • ravanwijk's avatar
    ravanwijk
    Copper Contributor
    Nov 30, 2021
    SergeiBaklan
    Amazing, simply amazing. This is what I was looking for. I was not aware it would be that complicated (and I am far from understanding what is going on!), but it definitely did the job! Thanks!!
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 30, 2021

      ravanwijk 

      You are welcome, glad it helped.

       

      The core here is MMULT function (microsoft.com) which is usually used as equivalent of COUNTIF() or SUMIF() when we work with arrays instead of ranges. Rest is mechanics , we have to combine 4 arrays (rows and columns headers, data and total) into one spill. Perhaps one day we will have built-in functions for such operations, when it will be much shorter.

Resources