CODEWITHSUNDEEP
excelexcel-formula
dimoss
dimoss

Reputation: 489

Count text occurrences in a column in Excel

I have the following list in Excel:

+-------+----------+
|  am   | ipiresia |
+-------+----------+
| 50470 |       29 |
| 50470 |       43 |
| 50433 |       29 |
|  6417 |       51 |
|  6417 |       52 |
|  6417 |       53 |
|  4960 |       25 |
|  4960 |       26 |
|  5567 |       89 |
|  6716 |       88 |
+-------+----------+

I want to add a column, let's say 'num' and count the occurrences of column 'am' in a row adding one when a new occurrence happens as follows:

+-------+----------+-----+
|  am   | ipiresia | num |
+-------+----------+-----+
| 50470 |       29 |   1 |
| 50470 |       43 |   2 |
| 50433 |       29 |   1 |
|  6417 |       51 |   1 |
|  6417 |       52 |   2 |
|  6417 |       53 |   3 |
|  4960 |       25 |   1 |
|  4960 |       26 |   2 |
|  5567 |       89 |   1 |
|  6716 |       88 |   1 |
+-------+----------+-----+

Is it possible to get this automatically with a formula in Excel?

Upvotes: 2

Views: 255

Answers (2)

EEM
EEM

Reputation: 6659

Assuming the sample table starts at A1 (with headers) the following formula will provide the expected results even if the list is not sorted.

=COUNTIF($A$1:$A2,A2)

Enter the formula at cell C2 then paste it down to the last cell of the data (or use AutoFill)

Upvotes: 0

Falco Alexander
Falco Alexander

Reputation: 3332

yes,

my example:

(assume you start your table containing 3 columns at Excels origin at A1 without header lines)

Then fill C1 with value "1" and then start in C2 with entering a formula simple like this:

=if($A2=$A1;$C1+1;1)

then you drag C2 down at the cells downright located autofill position as far as you want. Most times also double click works to let Excel autofill the columns down to the end of you prefilled table.

If you need assistance for AutoFill press F1 in Excel an the help with tell you in detail.

Upvotes: 2

Related Questions