I Understand there are many similar questions, but i cant find the answer anywhere.

My problem is that have 40.000 rows in an excel document, and i need to count how many times each requester appears in a column. picture of example

I have found the the function "COUNTIF()", but as i understand it needs a criteria. As there are 40.000 rows, i dont want to go all of them through to get the criteria for the fucntion and waste my time.

Is there a function to count all marked cells/marked column and then sorts them so it displays in a table of some sorts how many times each name appears?

  • Why not sort your list first and count them afterwards when they're grouped together?
    – LPChip
    Aug 1, 2018 at 11:47
  • Fx=COUNTIF(E:E,Ex). For example, in F2 it is =COUNTIF(E:E,E2).
    – Akina
    Aug 1, 2018 at 12:04
  • because then i still have to go through 40.000 rows
    – RatzMouze
    Aug 1, 2018 at 13:28
  • A Pivot Table would serve this purpose. Add Requester to both the Rows and Values section and it will give you a sorted, unique listed of Requesters and a count of how many times they appear in the table. Nov 18, 2021 at 16:52

2 Answers 2


If you want to use the countif.

Add a new column with this formula:

=COUNTIF($E$2:$E$40000, E2). After the formula show the result, triple click the bottom right corner of the cell to down copy the formula for the 40 000 cells. The value contain in this cell going to be the total count of the value in column E for all the column.

Hope this help

Source: https://www.extendoffice.com/documents/excel/2414-excel-count-cells-with-specific-text.html

  • please be aware that excel (i dont know if this is in orther versions than excel 2010) changes language/syntax if your excel is in anorther language. Fix this problem by googleing the english syntax you know and then "in @ownlanguage" or change the regional settings to english.
    – RatzMouze
    Aug 2, 2018 at 5:14

Use the search in Excel. Shortcut CTRL + F and Find All will count it for you.

  • Yes, it will find it for me, but as i statet... there are 40.000 rows. So if anyone knows a way to get it a bit more automated than the search function, i would really appreciate it.
    – RatzMouze
    Aug 1, 2018 at 11:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .