Excel formula Assistance--Help

[Biggy_E]

Hi! Excel Community!,

Looking for your assistance in how to create a formula that does the following:

IF  cell = 2023 or 2024, then

Sumifs (based on the criteria i selected), otherwise,

Sumifs (based on the criteria i selected)

Having a difficult time achieving this, need your assistance.  (See attached) , Please note that cell 14 is a drop down list.   My goal to sum the total revenue by Utility, Productivity, and Game based on the year i chose from drop list.   Lastly, data tabs were converted to tables.

[#2]

@Biggy_E 

I believe there are syntax errors in your formula.

In cell C15 in "Report1" tab, you can use below:

=IF(
    C$14 = "2024",
    SUMIFS(
        datatable_24[Revenue],
        datatable_24[Division], Report1!$B15
    ),
    SUMIFS(
        datatable_23[Revenue],
        datatable_23[Division], Report1!$B15
    )
)

Or better, you can use INDIRECT to convert a table name to a range.

Like what I have done in sheet2 in the attached sample:

[#3]

@Biggy_E 

Another approach would be to select data for the relevant year before starting the calculation.

= LET(
    datatable, SWITCH(year, 2023, datatable_23, 2024, datatable_24),
    revenue,   TAKE(datatable,,-1),
    division,  TAKE(DROP(datatable,,1),,1),
    SUMIFS(revenue, division, divisionList)
  )

Alternatively, XLOOKUP will return Ranges

= LET(
    datatable, SWITCH(year, 2023, datatable_23, 2024, datatable_24),
    revenue,   XLOOKUP("revenue",  headers, datatable),
    division,  XLOOKUP("division", headers, datatable),
    SUMIFS(revenue, division, divisionList)
  )

[#4]

I don't like switch, but I like SUMIFS.

[#5]

Hi @rachel 

Out of curiosity, what do you dislike about SWITCH?  Would you prefer CHOOSE or is it something more profound?

= LET(
    indexNum,  MATCH(year, {2023;2024}),
    datatable, CHOOSE(indexNum, datatable_23, datatable_24),
    revenue,   XLOOKUP("revenue",  headers, datatable),
    division,  XLOOKUP("division", headers, datatable),
    SUMIFS(revenue, division, divisionList)
  )

What my formulae set out to do was to explore the idea of selecting the relevant ranges prior to building a SUMIFS rather than applying the function to each table and then picking out the desired result.

[#6]

@PeterBartholomew 

Whether using SWITCH is a good practice or not has been a never ending debate among programmers. 

https://softwareengineering.stackexchange.com/questions/288848/bad-practice-switch-case-to-set-envir...

My personal preference is using external code for configuration. but as long as it works, and people can understand what the code is doing easily, I am OK with it.