Counting words

  • Thread starter Thread starter Chris Grant
  • Start date Start date
C

Chris Grant

Hi,
Is there a way in excel which the sheet can display the
amount of "yes" words in a list? I have tried numerous
solutions and they all seem to fail.
Thanks a lot,
Chris Grant
 
Chris,

If your cells have only one instance of "Yes", and that is all there is:
=COUNTIF(A:A,"Yes")

If you can have multiple Yes values in individual cells
=(SUMPRODUCT(LEN(A1:A100))-SUMPRODUCT(LEN(SUBSTITUTE(A1:A100,"Yes",""))))/3

HTH,
Bernie
MS Excel MVP
 
...
...
If you can have multiple Yes values in individual cells
=(SUMPRODUCT(LEN(A1:A100))-SUMPRODUCT(LEN(SUBSTITUTE(A1:A100,"Yes",""))))/3
Click to expand...
...

Or use just one SUMPRODUCT call and generalize beyond 'yes'.

=SUMPRODUCT(LEN(Rng)-LEN(SUBSTITUTE(Rng,TextSought,"")))/LEN(TextSought)
 
Back
Top