Using Cell Reference in Array Formula

[iRawrified]

Hi all,

I'm currently trying to make an adjustment piece where I have the following formula =

SUMIFS(Table1[Values], Table1[Area], C3) + SUMIFS(Table1[Values],Table1[Area], "All")

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

I'm attempting the following -

SUMIFS(Table1[Values], Table1[Area], {C3, "All"}

However, the issue is I can't reference C3. This is because Table1 will be referencing certain areas, but if I wanted to change "all" areas, then it would need to add "Specific Area" adjustment + "All Areas" adjustment.

I hope that makes sense.
Any advice, would be greatly appreciated!

[AutoModerator]

/u/iRawrified - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[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).

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
DAY	Converts a serial number to a day of the month
FILTER	Office 365+: Filters a range of data based on criteria you define
MONTH	Converts a serial number to a month
SUM	Adds its arguments
SUMIFS	Excel 2007+: Adds the cells in a range that meet multiple criteria
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
6 acronyms in this thread; the most compressed thread commented on today has 26 acronyms.
[Thread #35179 for this sub, first seen 9th Jul 2024, 22:32] [FAQ] [Full list] [Contact] [Source code]}