Forum Discussion

Biggy_E's avatar
Biggy_E
Copper Contributor
Apr 04, 2024

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 (b...
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor

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)
  )
rachel's avatar
rachel
Steel Contributor
Apr 04, 2024
I don't like switch, but I like SUMIFS.
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Apr 04, 2024

    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.

     

Resources