Multiple Look-up in Excel

[ba4crm]

Hi,
My table layout looks like:
Row A B C D E
Col UW UV UX UY UZ
1 Count 5 6 8 6 3

The codes (UW) belong to agents. Some agents have more than one code. I would like to put the above data into the following layout so that each agent has the total of all their codes listed together:
Row A B C
Col (UW+UV) UX (UY+UZ)
1 Count 11 8 9

How can I make this work? I have tried putting formula in the second table (=A1+B1), but if I change the agents, I have to re-do the formula. There are 66 codes which belong to 42 agents.

Thank you for your help

 

[rvnguy]

but if I change the agents, I have to re-do the formula

The obvious s not to change the agents...otherwise I am unclear what it is you are seeking???

Do you want this to function no matter what you change??

rvnguy
"I know everything..I just can't remember it all

 

[Gavona]

Easiest to maintain if you have another row identifying the agent. Then you can do a Sumif.

Or better, transpose the data, (copy, pastespecial...) columns for agent, code and count. And use a pivot table.


Gavin