Simplifying SUMIFs formulas

X M

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..

Purnima Gore

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

X M

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..