Total Occurrences in a Range (2000)

Topic

New Reply

Hi, I am wanting to count the number of times a particular string of data occurs within a range named chargedesc. I pulled the following formula from a Walkenbach book but get a Name? when I enter.

Formula is: =(SUM(LEN(chargedesc))-SUM(LEN(SUBSTITUTE(chargedesc, text, “”))))/LEN(text)

Any ideas?

Reply | Quote

Replies

You must either replace text with a quoted string representing the text string whose occurrences you want to count, or you must have put the text string in a cell named text.

And the formula is an array formula, you must confirm it with Ctrl+Shift+Enter instead of just Enter.

Reply | Quote

Hans, thanks. Maybe I was not totally clear and this may change your reply. In Column F I have around 10000 string entries that are basically sentences such as “Research the internet for case material”. However, there may be 900 other unique entries in column F. What I’d like to do is know how many times each one of these strings occurs. Will your modification do this?

Reply | Quote

The formula from John Walkenbach’s website counts how often a string occurs as part of the contents of one or more cells. For example, the string “do” occurs 3 times in these cells:

The dodo is extinct

Yes, I do!

If I understand your description correctly, you are not looking for part of cell values. Do you have a list of unique entries, or do you want to know for EACH entry in column F how often it occurs? Or am I completely off?

Reply | Quote

No. You are right on. I want to know how many times a the entire cell contents occur within a range. For example,

f2: LONG DISTANCE TELEPHONE CHARGES: PHONE TO ABC123456
f3: LONG DISTANCE TELEPHONE CHARGES: PHONE TO 205759xxxx
through f10000

I’d like to know how many times for example row 2 (exact match) was found in the range of f1-f10000.

Make sense?

Reply | Quote

In cell G1, enter the formula

=COUNTIF($F$1:$F$10000,F1)

and fill down to G10000 (you can use another column than G, of course)

Reply | Quote

Hans, thanks SO much. Works perfect

Reply | Quote

Oops. Spoke too soon. Doesn’t completely work. For example, using this forumula it appears that:
cell f2 – The cow jumped over the moon
cell f3 – The cow jumper over the moon and then over mars.

Will equal each other. I wanted exact matches. For example, cell f2 – how many times did this exact verbiage – with nothing before or after occurs.

Any suggestions?

Reply | Quote

I don’t understand that. COUNTIF only counts exact matches, not partial matches. If you had only F2 and F3, the COUNTIF formula would return 1 in both G2 and G3, since each value occurs only once. Please post a sample workbook if it doesn’t work that way for you.

Reply | Quote

Hans, sorry. You are right, as usual. I pasted the formula in the wrong cell (1 cell below where it should have been).

My apologies and thanks.

Reply | Quote

Never mind, could happen to anyone.

Reply | Quote