# Excel - Count by Group, Last Name, First Name

I have a spreadsheet, where an individual can be assigned up to four different groups (1 - 4). Column A is Group, Column B is the Last Name, and Column C is the First Name.

The individual is awarded \$10 spending budget for each group s/he is in.

Please offer how I can count per individual the number of groups s/he is engaged in (1 - 4 possible).

Thank you.
Asked:
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
can you post a screenshot of an example or a file with an example?
0
Author Commented:
Here is an example:

Group                Last Name             First Name
1                            Doe                        John
1                            Smith                     Jane
2                            Doe                        John
2                            Smith                     Jane
3                            Doe                        John
3                            Williams                Mary
4                            Doe                        John

Therefore, the count I would want to see is

Doe John - 4
Smith Jane - 2
Williams Mary - 1

Hope this made sense.

Thank you.
0
Author Commented:
I can use a pivot table - but I am not able to have two  resulting columns as follows (I filter by the GROUP (1 - 4)):

LAST NAME       FIRST NAME    COUNT

Instead, with the pivot table, I see that FIRST NAME becomes a subcategory of LAST NAME that has to be expanded with the + sign

LAST NAME           COUNT
0
Author Commented:
In a sense what I want to see is a three column table, where the last name can have duplicates since more than one person may have the same last name

LAST NAME       FIRST NAME         SUMCOUNT of GROUP
Williams              John                         2 (this person is in Groups 1 and 4)
Williams              Troy                         3 (this person is in Groups 1, 2, 3)

etc,
0
Commented:
Okay, Thanks for that example,

A simple way is this:

Assuming a name is only repeated when it is in another group, you can use a count formula to see how many times it appeared in a name column. e,g

=COUNTIF(B2:B8, "Smith") will return how many times Smith appears in the name column, which is also the same number of groups Smith is in. I have attached a file for you to check out.
Countgroups.xlsx
0
Commented:
I just saw your comment about last names and you are right so to get a better count, you can add a helper column that uses full name then apply the count on that column. See attached file.
It's a small alteration but it works way better.
Countgroups.xlsx
0
Finance AnalystCommented:
You can use Pivot Table.

Create the pivot and then right click on it and choose Pivot Table Options. Select the Display tab and choose Classic View. This will move all Header Row values into separate columns, ie one for Last Name and one for First Name.

When in the Pivot there should be an extra group on the RIbbon "Pivot Table Tools". In this group choose Design tab and select Report Layout button at left hand end, from Dropdown select "Repeat All item lables". The Last Names will now show all entries; ie "Doe | John" and "Doe | Jane" will have Doe displayed.

Thanks
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thank you.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.