# 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?

Xavier..

Tags:

• ✭✭✭✭✭✭

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

https://www.cierr.com

• ✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!