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 start introducing Logic OR into it, things no longer work, since the usual setup of such formula would be SUM(SUMIFS(.....)): this formula no longer spills the results.

Attached is an example file. The table is composed of months to the left and regions at the top. The data for the formula to look into is in the "Data" sheet. The OR logic condition is defined at the top of the table. In this case, the formula has to satisfy (amongst others) those entries that satisfy the forecast condition of "Risk" OR "Secure" OR "Upside". The forecast conditions are dynamic, as such, the formula requires to have OR Logic using cell references.

Appreciate your view / help!

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

6 Replies

  • 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.

    • 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