Counting the total number of cells with specified condition(freque

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
Can anyone pls tell me how do i count the total number of cells that has
a certain word appear twice or nth times in a column?

The below-mentioned will be the end result. The frequency will be specified
by the user and excel will count the number of cells that has a certain word
appearing twice in a column. Likewise, for Freqeuncy 3, sums the number of
cells with that has a certain word in a string that appears thrice in the
column.

Frequency(input) Number of occurences(output)
2 10
3 \30
 
Couldn't quite understand your final output,
but here's something which could help you along the way ..

Assume you have in Sheet1, in A1:A10, the data below

text1 text2
text1 text3
text1 text4
text1 text2
text1 text3
text1 text4
text1 text2
text1 text3
text1 text4
text1 text5

In Sheet2, you have listed in A2:A6

text1
text2
text3
text4
text5

You could put in B2:
=SUMPRODUCT(--ISNUMBER(SEARCH(A2,Sheet1!$A$1:$A$10)))
and copy B2 down to B6

Col B will return the count of the # of cells in Sheet1's A1:A10
for the corresponding text in A2:A6, viz.:

text1 10
text2 3
text3 3
text4 3
text5 1
 
With your list in column A (from cell A1 to cell A85):
In cell B1:
=SUMPRODUCT(--($A1=$A$1:$A$85))
Copy down to cell A85.
Cells D1 to D10 contain the numbers 1 to 10.
In cell E1:
=SUMPRODUCT(--($B$1:$B$85=$D1))
Copy down to cell E10.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top