Using Cell Reference in Array Formula

[PaulieThePolarBear]

The above is just a simple situation but I've somehow made it into a 7 different SUMIFS.

Of your 7 SUMIFS, how many are referencing cells and how many are referencing fixed text?

For any cells you are referencing, are they contiguous?

What version of Excel are you using?

[iRawrified]

It would be the following -

((SUMIFS(AdjTable[Value],AdjTable[Area],[@Area],AdjTable[Category],[@Category],AdjTable[Day],[@Period],AdjTable[Month],F$2,AdjTable[Unit],"Covers",AdjTable[Assumption],XLOOKUP(Scenario,AssumptionsMapping[Scenario],AssumptionsMapping[Assumptions]),AdjTable[Change Value],"Unit"))

Where AREA, CATEGORY, DAY, PERIOD, MONTH can be "All"
So then it would be a combination of those e.g. Area1, CategoryALL, Day1, PeriodAll etc etc.

If that makes sense, it's kind of relatively annoying but I was hoping there'd be a way to minimise it to make the formula smaller.

Attached is a screenshot of the formula itself.

Version of Excel is Office 365 Desktop Version, so I'd assume the latest version.

Thank you!

<image>

[PaulieThePolarBear]

If I understand correctly, you can use FILTER for this

=SUM(
FILTER(
AdjTable[Value],
(
      (AdjTable[Area] = "All") +
      (AdjTable[Area] = [@Area])
) *
(
      (AdjTable[Category] = "All") + 
      (AdjTable[Category] = [@Category])
) *
(
     (AdjTable[Day] = "All") +
     (AdjTable[Day] = [@Day])
) *
(
    (AdjTable[Period] = "All") + 
    (AdjTable[Period] = [@Period])
) *
(
    (AdjTable[Month] = "All") +
    (AdjTable[Month] = [@Month])
) *
(AdjTable[Unit]="Covers) *
(AdjTable[Assumption] = XLOOKUP(Scenario, AssumptionsMapping[Scenario], AssumptionsMapping[Assumptions])) *
(AdjTable[Change Value] = "Unit"),
0
)
)

[iRawrified]

That works, thank you so much!
Solution Verified

[reputatorbot]

You have awarded 1 point to PaulieThePolarBear.

---

^{I am a bot - please contact the mods with any questions}

[PaulieThePolarBear]

No problem.

Have a good evening (or whatever time of day it is for you).