How to combine multiple SUMIFS (Name Manager)

[swtone]

Hello, I am currently working with SUMIFS and COUNTIFS multiple conditions and wondering if there is a way to make the formula shorter.

Basically I have 2 groups of conditions; the first one is time conditions which is use repeatedly and second are other conditions such as gender, product category etc. that keep changing depend on what we want to find out in those time period.

Currently I repeat all condition every time I want to find out something.
Put it into 1 formula (as below capture) using IFS (time value match) + SUMIFS(each time condition and other condition) and that make it every long. If I want to find out something else besides gender for example, I have to repeat the whole IFS and all SUMIFS time condition again and change other conditions.

I'm thinking, if I am able to put the times condition (All time, year, month, week, day, today) in Name Manager and call that argument instead of typing the whole thing out every time that would make it shorter and easier. However, I couldn't find the way to put that idea into practice. Any recommendation?

How do I rephrase my formula so that I can use Name Manager to help with time conditions. Please find the excel file in attached.

Thank you so much!

Capture.PNG

[Bo_Ry]

Please try

Please Login or Register  to view this content.

[swtone]

That's one interesting formula. Thank you! I assume the Name Range Idea isn't practical, is it?

[swtone]

May I ask what is ,0 and ,9^9 at the end of the expression do?
And if I create a workbook using Switch function as a part of the formula, when I share it with people who use older versions will it still working or they will see error?

Thank you!

[TMS]

See: https://support.microsoft.com/en-us/...5-d532ec4aa25e

Short answer: yes it will error on older versions of Excel

[Bo_Ry]

date >=0 and date <=9^9 => All Time


For old version

Please Login or Register  to view this content.

[swtone]

date >=0 and date <=9^9 => All Time


For old version

Please Login or Register  to view this content.

Thank you so much for your help!

[swtone]

Short answer: yes it will error on older versions of Excel

Thank you! I wasn't sure because I once created a workbook using UNIQUE function and the person opening the workbook doesn't have 365 nor 2019 version but it didn't show errors. Inside the formula box look difference than what I see on my 365 though.