# Excel formula Assistance--Help

Copper Contributor

# Excel formula Assistance--Help

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.

5 Replies

# Re: Excel formula Assistance--Help

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:

# Re: Excel formula Assistance--Help

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),
SUMIFS(revenue, division, divisionList)
)``````

# Re: Excel formula Assistance--Help

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

# Re: Excel formula Assistance--Help

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

# Re: Excel formula Assistance--Help

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