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 (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.

Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Apr 04, 2024

    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.

         

  • rachel's avatar
    rachel
    Steel Contributor
    Apr 04, 2024

    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:

     

Resources