Nov 30 2021 12:18 AM
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!
Nov 30 2021 03:07 AM
=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?
Nov 30 2021 05:42 AM
@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.
Nov 30 2021 06:36 AM
@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.
Nov 30 2021 09:12 AM
SolutionI 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"
)
Nov 30 2021 09:43 AM
Nov 30 2021 12:39 PM
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.
Nov 30 2021 09:12 AM
SolutionI 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"
)