# sum values based in agent ID

I have a sheet with agent id's in column a and under each ID is a sub identifier with a value in column b of 1 - 7 sub identifiers are Break_1000 Meeting_2000 Training_3000 Personal_4000 System_Outage_5000 Activations_6000 Outbound_7000 Using the sub values, any agent ID may have anywhere from 1 to 7 of the above identifiers. and I would like a way to sum each Agent ID (a:a) by sub identifier

The results I expect would be E13 Agent ID_1008, F13 meeting_2000, G13 outbound_7000 F14 2, G14 7, Row 13 would have columns containing the ID's and Row 14 would contain the values associated with those ID's make sense?

Unfortunately I can't comment yet since I believe the question needs a bit of clarification.

If there are multiple sub_ids are they all in the same row? or is there multiple rows for an agent_id.

If you just want the number of agents with a given sub_id you could use countif.

``````=countif(B:B,sub_id)
``````

If they are all in the same column you could just find what row they are in using something like:

``````=row(index(A:A),match(AGENT_ID,A:A,0)))
``````

This would give the rows for each one and then you would need to find the row that is 1 higher and subtract your row from this Assuming you have a list of agents in column D and a their row calc in column E.

``````=small(E:E,RANK(E2,E:E,1)+1)-E2-1
``````

Note this won't work for the last agent since there is nothing to subtract from.

Otherwise I would suggest setting up the table in a more usable format, storing multiple types of data in one column is generally poor data structure and generally makes it tough to work with.

Gordon

• I think you still have an issue with what your calling row vs column. You say their all in the same row but the comment has everything in column A with everything in different rows. – gtwebb Dec 3 '13 at 17:41
• Sorry about that you are correct they are all int he same column. the values are in column B which could be anything as long as each Sub ID and sub id value, is matched to each agent ID – tom cole Dec 3 '13 at 17:43