# Matching names and summing another column at the same time

#### BrettKelly

##### New Member
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>

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### alvin-chung

##### Active Member
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

##### New Member
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?

Last edited:

#### alvin-chung

##### Active Member
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

##### New Member

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!

##### MrExcel MVP
"I have been trying for 6 months..."

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

#### alvin-chung

##### Active Member
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

##### New Member
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!

Replies
19
Views
397
Replies
2
Views
438
Replies
2
Views
85
Replies
4
Views
79
Replies
4
Views
159

1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back