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...
I don't like switch, but I like SUMIFS.
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.
Whether using SWITCH is a good practice or not has been a never ending debate among programmers.
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.
