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
Check out the Formula Handbook template!