Simplifying SUMIFs formulas
Hi
In my summary sheet, I am calculating the total of all CEs value for all my Agents using the following formulas:
=SUMIFS([CEs:CEs], [Agents:Agents], "Agent1") + SUMIFS([CEs:CEs], [Agents:Agents], "Agent2") + SUMIFS([CEs:CEs], [Agents:Agents], "Agent3") + SUMIFS([CEs:CEs], [Agents:Agents], "Agent4")
and it gives me the number expected.
I am trying to simplify this formulas using =SUM(SUMIFS([CEs:CEs],[Agents:Agents], {"Agent1","Agent2","Agent3","Agent4"})) but it returns me #UNPARSEABLE.
Anybody struggled with that formula?
Thanks in advance for your help
Xavier..
Answers

Hi
Am assuming that you have the rest of 2020 within the table that I can't see because the totals for February and January would not add up to 4488.
That said, why don't you indent the agent row underneath and then use =sum(children())
That way you get the same effect without having to reference the agents, just what is underneath
Here is the way to do this
Indent the agents row below,, I have indented one level. In January I have added some arrows to show you that I have indented. In February I have added the agents below the month.
You have already indented the months under the year. So use the same formula where you need the totals and if you add more agents, as long as they are indented the same under the parent correctly they will add up.
Hope that helps.
ATB
PG
Purnima Gore
Cierr Limited
Your Time is Important, you want to Stay on Track, We can help you use the Right Tools

Hi Cierr
thank you for your reply however this is not what i am looking for. I want to use only one formula that can sum the CEs for all agents that is simpler than the one I currently use
=SUMIFS([CEs:CEs], [Agents:Agents], "Agent1") + SUMIFS([CEs:CEs], [Agents:Agents], "Agent2") + SUMIFS([CEs:CEs], [Agents:Agents], "Agent3") + SUMIFS([CEs:CEs], [Agents:Agents], "Agent4")
Thanks
Xavier..
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!