Automatic way to enter data into COUNTIF formula?

[sophiagymgall]

Hi all,

I'm new to Excel and need some help regarding the COUNTIF formula I'm using. I have a huge (for a newbie ) spreadsheet where every item is associated with several key words. There about 500 key words, all in the same column, and I have to build a table identifying the total frequency of each key word. Basically, the first column of the table I've created lists all the possible key words, and the second one is all the COUNTIF formulae, each one being associated with its corresponding key word. The formula I need to use is this one :

=COUNTIF($D$2:$D$8486;"corresponding key word from column 1")

The formula works well and my table looks fine, but the task of copying and pasting 500 key words into each occurrence of the formula is pretty daunting! This is where I need some help - is there an automatic way to enter each key word into the corresponding formula without having to do it manually? Otherwise, is there a more direct way or another formula that would give me the information I need? Surely there's a more efficient way to do this, but I just don't know how!

I'd post the document here, but it's confidential.

Thanks for reading, and thanks in advance for your help

[Jonmo1]

Put each of your key words into a range of cells, say G2:G10

Then use
=COUNTIF($D$2:$D$8486;G2)
And fill down.