Forum Discussion
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...
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)
)
I don't like switch, but I like SUMIFS.