Count cells with string

Hi,

I am trying to count in Calc all the cells with a given string. Say that my string is in cell E2, I am trying to count all the occurrences of that string in B7:R29. I tried using COUNTIF(B7:R29, E2) but it does not work.

Is there any body that has any idea how to do this?

Thanks a lot!

Alberto

Hi Alberto,
Do you need already available formula or macro?

Your formula =COUNTIF(B7:R29;E2) is exactly as it should be (with the posible exception that absolute addressing regarding the range might be preferrable).
What does the cell you entered the formula in show? (“It does not work” is not actually a useful information. If it worked you did not ask.)

Please do not post as community wiki. It helps no one.

If what you search is only part of the strings in the searched cells, perhaps you have enable the option:
Menu/Tools/Options/LibreOffice calc/Calculate - Search criteria = and <> must apply to whole cells.
if disable works for me.
But with this option disable there it’s a bug that makes calc slow searching string lists, specially visible with large ranges for search.
If this is the case you can use regular expressions with the option enable.
:=COUNTIF(B7:R29;".*"&E2&".*")
Only be aware there are no characters to search that are part of regular expressions.

Hi

To get the result regardless of options, you can use (not case sensitive):

=SUMPRODUCT(ISNUMBER(SEARCH(E2;B7:R29)))

or (case sensitive)

=SUMPRODUCT(ISNUMBER(FIND(E2;B7:R29)))

Regards

You can achieve it using COUNTIF or COUNTIFS. Details you can find here: