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.
exp vgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Tj aCommented:
can you post a screenshot of an example or a file with an example?
exp vgAuthor 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.
exp vgAuthor 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)):


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

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

exp vgAuthor 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

Williams              John                         2 (this person is in Groups 1 and 4)
Williams              Troy                         3 (this person is in Groups 1, 2, 3)

Tj aCommented:
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.
Tj aCommented:
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.
Rob HensonFinance 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.

Rob H

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.

Start your 7-day free trial
exp vgAuthor Commented:
Thank you.
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.