Matching names and summing another column at the same time

BrettKelly

New Member
Joined
Jan 8, 2014
Messages
4
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 NameProperty Status=codeC=67L=76N=78P=80
BobC=code(B2)=if(B2=67,1,0)sub 67 for 76sub for 78sub for 80
BobC=code(B3)=if(B3=67,1,0)
BobL=code(B4)=if(B4=67,1,0)
BobN=code(B5)=if(B5=67,1,0)
RayL=code(B6)=if(B6=67,1,0)
RayN=code(B7)=if(B7=67,1,0)
MartinP=code(B8)=if(B8=67,1,0)
MartinU=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
Joined
Nov 24, 2013
Messages
361
Welcome to the forum, I think you can easily display the sum for each agent with different status using Pivot Table.
Using table below:

AB
1Agent NameProperty Status
2BobC
3BobC
4BobL
5BobN
6RayL
7RayN
8MartinP
9MartinU

<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 LabelsCLNPUGrand Total
Bob2114
Martin112
Ray112
Grand Total222118

<tbody>
</tbody>
 

BrettKelly

New Member
Joined
Jan 8, 2014
Messages
4
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
Joined
Nov 24, 2013
Messages
361
Thank you for your feedback, you're welcome :cool:

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
Joined
Jan 8, 2014
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"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
Joined
Nov 24, 2013
Messages
361
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 :cool:
 

BrettKelly

New Member
Joined
Jan 8, 2014
Messages
4
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!
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top