-1

I am quite new to Excel formulas and I am stuck on something. Let's say I have this table:

enter image description here

What formula can sum all strings to number of occurrences in each column, something like this:

enter image description here

The result does not have to be in this exact format, but should be understandable.

I.Klein
  • 41
  • 5

1 Answers1

0

Try this format layout

In A9, formula copied down :

=IFERROR(INDEX(A$2:A$7,MATCH(0,INDEX(COUNTIF(A$8:A8,A$2:A$7),0),0))&"","")

In B9, formula copied down :

=IF(A9="","",COUNTIF(A$2:A$7,A9))

Then, select A9:B13 >> copy/paste to C9:D13

enter image description here

bosco_yip
  • 3,762
  • 2
  • 5
  • 10
  • Maybe I did somthing wrong, but I can't work it out as expected. Your formulas only count the first name (e.g John), but I can't do it for all the other names... – I.Klein Feb 08 '20 at 17:44