Counting Cells meeting certain criteria

Hi,

I have a worksheet that has a list of terms in one column, that vary from 1 to 5 words in length. I want to be able to put in a formula that will detail how many words in the list have 1 word, how many have 2 words, etc.

I also have a list of conditionally formatted cells that show even H/M/L and are colour coded as such. I want also to do a similar thing with these and be able to show how many cells have H, how many have M, and how many have L.

This is also part of a filtered list. If I apply a filter, how can I make the formula do a subtotal so that it only shows the count for the list that is filtered? I know how to do a SUBTOTAL for a filtered list, however not sure how you would apply this to a count of the cells as above.

I have attached an Excel 97 workbook with an example of what I am trying to achieve.

Thanks in advance

As far as I know there's no built in function to count words in a cell or range of cells.
As a work around you can count spaces in a cell, then add 1.... but that will only work if there is only 1 space between words, and no leading or trailing spaces.

Something like this can put you in the right direction:

Please Login or Register  to view this content.

manohoo, you are on good path:
From your formula you just need to combine for whole range:

=SUMPRODUCT(--(LEN($F$3:$F$9)-LEN(SUBSTITUTE($F$3:$F$9," ",""))+1=ROWS(F$11:F11)))

Ozwilly, please don't open duplicated posts.

Here is attached example: http://www.excelforum.com/excel-gene...-criteria.html

Thanks everyone, and my apologies, I did research the forum but did not realize it was a duplicated topic

One one other issue which I mentioned, is using this formula in a filtered list. How would I modify it so that it only counted the entries in the filtered list???