I'm not sure whether you noticed, but my solution does give you a count for each word; the problem is that it doesn't give you that count on every row, only on the last row for each word. You can change it around to have it give you the count on the first row of every word, if you prefer.

Here's what's going on: COUNTIF works, alright, except that there are 270 000 rows and therefore 270 000 COUNTIFs, and each one of those 270 000 COUNTIFs has to count all 270 000 rows. That's why it's taking so long. Let's call that solution #1.

Once it's sorted, using the code I gave you—call that solution #2—should work a lot faster. But instead of the answer laid out the way you wanted, it would look like this:

Now, there are two other ways I can think of to do this:

3) In column C use the formula I gave you: "=IF($A2=$A1,$C1+1,1)". Then in column B, use this: "=IF($A2=$A3,$B3,$C2)". Column C counts up the number of each word, just as with my formula in solution #2. But column B looks at column C to display the word count on every row, the way you have it in solution #1; it just works a lot faster. At least, I think it will; I didn't test it with 270K rows.

4) Write a VBA program. This is probably the way I would have done it in the first place, because I'm a programmer at heart. The nice thing about this solution is that it'll work fast and you don't have to sort the words first. Also it can put the the answers in a separate sheet, with one row for each word, as you suggested. The only wrinkle is that you have to be able to write a little program. We can show you how, but you don't want to just copy it; you pretty much have to take the time to learn how it works.

this works for you IN THE SENSE that it gives you a count of each word—only it's not on every row, just on the last row for each word. You could make it on the first row, if you prefer. The problem with your list is that if COUNTIF