SUMIFS with OR Logic and spilling results

[ravanwijk]

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!

[#2]

@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?

[#3]

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

[#4]

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

[#5 · Accepted solution]

@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]

@Sergei Baklan
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!!

[#7]

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