Programmatically count how many times ALL values in a column appears from an excel document

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.

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? Aug 1, 2018 at 11:47
• `Fx=COUNTIF(E:E,Ex)`. For example, in F2 it is `=COUNTIF(E:E,E2)`. Aug 1, 2018 at 12:04
• because then i still have to go through 40.000 rows 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

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

• 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. 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. Aug 1, 2018 at 11:51