Simple Count question

[CHML]

Hi all.
How do I do a Count of groups per last name? attached is part of a spreadsheet. E2 through E7 is one group for Name 1, E8 - E13 is a 2nd group for Name 1 - So Name 1 has 2 groups, Name 2 has one and so on.
I tried iserting a Pivot Table but that gives me a count of all cells in collumn E per Name (12 for name 1 etc.)

Thanking you in advance!

 

[AhoyNC]

Try:
With Excel 2019 you will need to enter with CTRL-SHIFT-ENTER. Then drag formula down the rows as needed.
Using CTRL-SHIFT-ENTER will put the"{}" around the formula (don't type them in).

Book2
	A	B	C	D	E	F
1	LAST NAME	FIRST NAME	Group		LAST NAME	Count of Groups
2	Last Name 1	First Name 1	IRE		Last Name 1	2
3	Last Name 1	First Name 1	IRE		Last Name 2	1
4	Last Name 1	First Name 1	IRE		Last Name 3	3
5	Last Name 1	First Name 1	IRE
6	Last Name 1	First Name 1	IRE
7	Last Name 1	First Name 1	IRE
8	Last Name 1	First Name 1	IRF
9	Last Name 1	First Name 1	IRF
10	Last Name 1	First Name 1	IRF
11	Last Name 1	First Name 1	IRF
12	Last Name 1	First Name 1	IRF
13	Last Name 1	First Name 1	IRF
14	Last Name 2	First Name 2	IMH
15	Last Name 2	First Name 2	IMH
16	Last Name 2	First Name 2	IMH
17	Last Name 2	First Name 2	IMH
18	Last Name 3	First Name 3	IMH
19	Last Name 3	First Name 3	IRG
20	Last Name 3	First Name 3	IRG
21	Last Name 3	First Name 3	IRG
22	Last Name 3	First Name 3	IRG
23	Last Name 3	First Name 3	IRG
24	Last Name 3	First Name 3	IRH
25	Last Name 3	First Name 3	IRH
26	Last Name 3	First Name 3	IRH
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=SUM(IF(FREQUENCY(IF($E2=$A$2:$A$26,MATCH($A$2:$A$26&"\"&$C$2:$C$26,$A$2:$A$26&"\"&$C$2:$C$26,0)),ROW($A$2:$A$26)-ROW($A$2)+1),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[awoohaw]

here is another way, also CSE entry:
(and you may need to change the SUM FUNCTION to SUMPRODUCT.

Mr excel questions 35.xlsm
				D	E	F	G	H
1				Last Name	Group
2				Last Name 1	1RE		Last Name 1	2
3				Last Name 1	1RE		Last Name 2	1
4				Last Name 1	1RE		Last Name 3	3
5				Last Name 1	1RE
6				Last Name 1	1RE
7				Last Name 1	1RE
8				Last Name 1	1RF
9				Last Name 1	1RF
10				Last Name 1	1RF
11				Last Name 1	1RF
12				Last Name 1	1RF
13				Last Name 1	1RF
14				Last Name 2	1MH
15				Last Name 2	1MH
16				Last Name 2	1MH
17				Last Name 2	1MH
18				Last Name 3	1XH
19				Last Name 3	1RG
20				Last Name 3	1RG
21				Last Name 3	1RG
22				Last Name 3	1RG
23				Last Name 3	1RG
24				Last Name 3	1RH
25				Last Name 3	1RH
26				Last Name 3	1RH
CHML

Cell Formulas
Range	Formula
H2:H4	H2	=SUM((--(G2=$D$2:$D$26))*((--($E$2:$E$26<>$E$1:$E$25))+(--($E$2:$E$26<>$E$3:$E$27))))/2

 

[awoohaw]

Here is the sumproduct way, and you may not need to use CSE entry.

Mr excel questions 35.xlsm
				D	E	F	G	H	I
1				Last Name	Group
2				Last Name 1	1RE		Last Name 1	2
3				Last Name 1	1RE		Last Name 2	1
4				Last Name 1	1RE		Last Name 3	3
5				Last Name 1	1RE
6				Last Name 1	1RE
7				Last Name 1	1RE
8				Last Name 1	1RF
9				Last Name 1	1RF
10				Last Name 1	1RF
11				Last Name 1	1RF
12				Last Name 1	1RF
13				Last Name 1	1RF
14				Last Name 2	1MH
15				Last Name 2	1MH
16				Last Name 2	1MH
17				Last Name 2	1MH
18				Last Name 3	1XH
19				Last Name 3	1RG
20				Last Name 3	1RG
21				Last Name 3	1RG
22				Last Name 3	1RG
23				Last Name 3	1RG
24				Last Name 3	1RH
25				Last Name 3	1RH
26				Last Name 3	1RH
CHML

Cell Formulas
Range	Formula
H2:H4	H2	=SUMPRODUCT((--(G2=$D$2:$D$26))*((--($E$2:$E$26<>$E$1:$E$25))+(--($E$2:$E$26<>$E$3:$E$27))))/2

 

[CHML]

Thank you so much, AhoyNC, & Awoohaw. You both did so great! This is from a formula perspective. Is there no option for such a Count using Pivot Table?

 

[awoohaw]

There may be a way that you can add a calculated field. Or there may be something with a "MEASURE" from Power Query. I am not an expert on these.
Mr. Excel has a forum for "Power Tools" that you may be able to find some information on Power Query.

 

[AhoyNC]

To use a pivot table.
Check the box to "Add data to Data Model"

Then add Last Name to Rows and Groups to Values in pivot table.
This will give you a count of the groups (not what you want)

Click on the header "Count of Group" and right click.

When you right click you will get the following options.
Choose "Summarize Values By" and then "Distinct Count". This will give you the unique count by Last Name.

Book2
	A	B	C	D	E	F	G
1	LAST NAME	FIRST NAME	Group
2	Last Name 1	First Name 1	IRE			Row Labels	Distinct Count of Group
3	Last Name 1	First Name 1	IRE			Last Name 1	2
4	Last Name 1	First Name 1	IRE			Last Name 2	1
5	Last Name 1	First Name 1	IRE			Last Name 3	3
6	Last Name 1	First Name 1	IRE			Grand Total	5
7	Last Name 1	First Name 1	IRE
8	Last Name 1	First Name 1	IRF
9	Last Name 1	First Name 1	IRF
10	Last Name 1	First Name 1	IRF
11	Last Name 1	First Name 1	IRF
12	Last Name 1	First Name 1	IRF
13	Last Name 1	First Name 1	IRF
14	Last Name 2	First Name 2	IMH
15	Last Name 2	First Name 2	IMH
16	Last Name 2	First Name 2	IMH
17	Last Name 2	First Name 2	IMH
18	Last Name 3	First Name 3	IMH
19	Last Name 3	First Name 3	IRG
20	Last Name 3	First Name 3	IRG
21	Last Name 3	First Name 3	IRG
22	Last Name 3	First Name 3	IRG
23	Last Name 3	First Name 3	IRG
24	Last Name 3	First Name 3	IRH
25	Last Name 3	First Name 3	IRH
26	Last Name 3	First Name 3	IRH
Sheet4

 

[CHML]

Oh! This is precisely what I was trying to gain! Thanks a million, AhoyNC.

 

[AhoyNC]

You're welcome. Thanks for the feedback.