Matching names and summing another column at the same time

[BrettKelly]

Hello everyone. I have a very unique, or what i think to be unique, problem. I have been trying for 6 months to figure out a formula to allow me to solve this problem and I cant seem to think of one. I will try to discribe this as simple as possible if you have any questions or I am not specific enough please post a reply and I will try to clarify my problem. So here it is.

I crunch data for a real estate company and something we need to know is how many properties each of our agents has. Also we need to know what status the property is in. We have about 8 different property statuses, 150 different agents and around 2000 properties. Each line in an excel document is a different property and the status and agent names are in columns. The process I use right now is very hands on and requires around 3 hours of work to finish up.

1) Currently I sort all properties by agent names
2) I use =Code(a), where a is the cell containing the property status, to convert the status to a number
3)Then i use =If( the cell mentioned above=97,1,0). I do this for all status on one row.
4) I drag these all the way down our 2000 pieces of data.
5) Then i manually sum each agents properties by looking at where the name starts and where it stops.
(I have included what this looks like below)

So If bob has properties 1 through 20 i will use =sum A1:A20. Then drag this across the columns to see how many of each property bob has. I do this for all 150 agents on 2000 lines of data.

What I would like to do is make a formula that can verify the agents name and sum the columns with the active property status. If you can be of any help please let me know. This takes up a large amount of my time and I cant figure out a formula to do it for me.

Agent Name	Property Status	=code	C=67	L=76	N=78	P=80
Bob	C	=code(B2)	=if(B2=67,1,0)	sub 67 for 76	sub for 78	sub for 80
Bob	C	=code(B3)	=if(B3=67,1,0)
Bob	L	=code(B4)	=if(B4=67,1,0)
Bob	N	=code(B5)	=if(B5=67,1,0)
Ray	L	=code(B6)	=if(B6=67,1,0)
Ray	N	=code(B7)	=if(B7=67,1,0)
Martin	P	=code(B8)	=if(B8=67,1,0)
Martin	U	=code(B9)	=if(B9=67,1,0)
Bob			=sum(D2:D5)	=sum(E2:E5)	=sum(F2:F5)	=sum(G2:G5)

<tbody>
</tbody>

 

[alvin-chung]

Welcome to the forum, I think you can easily display the sum for each agent with different status using Pivot Table.
Using table below:

	A	B
1	Agent Name	Property Status
2	Bob	C
3	Bob	C
4	Bob	L
5	Bob	N
6	Ray	L
7	Ray	N
8	Martin	P
9	Martin	U

<tbody>
</tbody>

Goto Insert menu > Pivot Table > Drag Agent Name to Row Labels, Property Status to Column Labels and Values will yield the following result:

Row Labels	C	L	N	P	U	Grand Total
Bob	2	1	1			4
Martin				1	1	2
Ray		1	1			2
Grand Total	2	2	2	1	1	8

<tbody>
</tbody>

 

[BrettKelly]

Dear Alvin,

This is almost exactly what i was looking for! Thank you very much. I am going to play with this and see if i can manipulate it how i need. You have just made my day.

Edit: One question though. I have agent's first name and last name in two different columns. Is there a way I can display the names next to each other instead of under a drop down menu?

 

[alvin-chung]

Thank you for your feedback, you're welcome

ps: I think simplest way would be adding a helper column to concatenate first name and last name, then use that column in the Pivot table (e.g. A1="Alvin", B1="Chung" create C1=A1&" "&B1 will get you "Alvin Chung")

 

[BrettKelly]

Thank you very much. I still have so much to learn on Excel. I sincerely appreciate the knowledge you have shared with me. I will be able to apply this to many other reports in our office and save us a lot of time and money. Also save myself a lot of unnecessary effort. Happy new years to you!

 

[PaddyD]

"I have been trying for 6 months..."

under no circumstances let this happen again! Anything over about an hour, come here

 

[alvin-chung]

Thank you very much. I still have so much to learn on Excel. I sincerely appreciate the knowledge you have shared with me. I will be able to apply this to many other reports in our office and save us a lot of time and money. Also save myself a lot of unnecessary effort. Happy new years to you!

I'm am glad to hear that it helps, happy new years to you too

"I have been trying for 6 months..."

under no circumstances let this happen again! Anything over about an hour, come here

Absolutely agree

 

[BrettKelly]

Thank you guys very much. Those 6 months were more experimenting with many different formulas. I spent far less than 6 straight months trying. I will definately be back on these forums to get more information from you guys. Thank you guys again so much!