count multiple values in a column in excel

Question

I hope everyone is doing well I have a question regarding whether we can count multiple values at once in excel as below:

what is in column

**Name**
a
a
a
b
b
c
a

what I want to count individuals at once as below:

**a**    **b**     **c**
4          2          1

Thank you in advance

@basic as where to put this column in pivot table to get desired output? — MaxMiak
– MaxMiak, Commented Sep 28, 2020 at 19:22
@MaxMiak if you want a pivot table just put the Name column in both columns and values. — Scott Craner
– Scott Craner, Commented Sep 28, 2020 at 19:31

Gary's Student · Accepted Answer · 2020-09-28 21:09:30Z

4

For Excel 365.

In C1 enter:

=TRANSPOSE(UNIQUE(A2:A8))

In C2 enter:

=COUNTIF($A$2:$A$8,C1)

and copy across.

EDIT#1:

Please note the excellent suggestions made by JvdV and chris neilsen. These improvements will allow column A to be modified (items added/removed) without having to go back and mod the formulae.

In C1 enter:

=TRANSPOSE(UNIQUE(FILTER(A:A,(ROW(A:A)>1)*(A:A<>""))))

and in C2 enter:

=COUNTIF(A:A,C1#)

edited Sep 28, 2020 at 21:09

answered Sep 28, 2020 at 19:23

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

VBasic2008 Over a year ago

Shouldn't the references be $A2:$A8?

Gary's Student Over a year ago

@VBasic2008 Great question! .................it turns out that in the new dynamic spillable formulas, the references do not propagate with the spill, so the $ are not needed.

chris neilsen Over a year ago

You can make the Count Spill with CountIf(A2:A8, C1#)

chris neilsen Over a year ago

And you can make the solution fully dynamic wit (in C1) =TRANSPOSE(UNIQUE(FILTER(A:A,(ROW(A:A)>1)*(A:A<>"")))) and (in C2) CountIf(A:A, C1#)

JvdV Over a year ago

Little fun alternative: =CHOOSE({1,2},TRANSPOSE(UNIQUE(A2:A8)),COUNTIF(A2:A8,TRANSPOSE(,UNIQUE(A2:A8))))

|

Collectives™ on Stack Overflow

count multiple values in a column in excel

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related