Excel, How many same types a repeating name has-CodePudding

I have a huge list of different car names which are repeating and their types. I need to sum the amout of types each car has. For example Name1 has 2 van and one medium, Name2 has 2 van 1 medium, Name3 has 1 non_cargo and 2 medium and so on. I don't know how to make a formula for that. I have uploaded example screenshot. Thank you in advance. enter image description here

CodePudding user response：

Without no excel version constraint you can use the following assuming your names are in the range A2:A10 for example:

=HSTACK(UNIQUE(A2:A10), COUNTIF(A2:A10,UNIQUE(A2:A10)))

It returns in the first column the unique names and the second column the corresponding counts for each unique names.

If you want to have a count by name and type better to use a Pivot Table like this:

CodePudding user response：

When using Office 365 you could use:

=LET(data,A1:B12,
          _d1,INDEX(data,,1),
          _d2,INDEX(data,,2),
     u,UNIQUE(data),
          _u1,INDEX(u,,1),
          _u2,INDEX(u,,2),
     m,MMULT(
             (TRANSPOSE(_d1)=_u1)* 
             (TRANSPOSE(_d2)=_u2),
             SEQUENCE(COUNTA(_d1),,1,0)
             ),
HSTACK(u,m))

In older Excel you can use the following:

In D2 use: =IFERROR(INDEX(A$1:A$12,MATCH(0,COUNTIFS($D$1:$D1,$A$1:$A$12,$E$1:$E1,$B$1:$B$12),0)),"") drag from D2 to E12 (or up to when you see empty values).

In F2 use =COUNTIFS($A$1:$A$12,$D2,$B$1:$B$12,$E2) and drag down.