Ask Your Question

Count cells with string

asked 2016-02-09 15:35:59 +0200

Alberto Franco gravatar image


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!


edit retag flag offensive close merge delete


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

shma_lo gravatar imageshma_lo ( 2016-02-09 19:16:39 +0200 )edit

4 Answers

Sort by » oldest newest most voted

answered 2016-02-09 22:40:47 +0200

m.a.riosv gravatar image

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.
Only be aware there are no characters to search that are part of regular expressions.

edit flag offensive delete link more

answered 2016-02-10 10:03:07 +0200

pierre-yves samyn gravatar image


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


or (case sensitive)



edit flag offensive delete link more

answered 2016-02-09 22:13:50 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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.)

edit flag offensive delete link more


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

Jim K gravatar imageJim K ( 2018-06-21 10:24:59 +0200 )edit

answered 2018-06-21 06:04:12 +0200

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-02-09 15:35:59 +0200

Seen: 16,545 times

Last updated: Jun 21 '18