0

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?

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.