Ask Your Question
0

Count cells with string

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

Alberto Franco gravatar image

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

edit retag flag offensive close merge delete

Comments

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
0

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.
:=COUNTIF(B7:R29;".*"&E2&".*")
Only be aware there are no characters to search that are part of regular expressions.

edit flag offensive delete link more
0

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

pierre-yves samyn gravatar image

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

edit flag offensive delete link more
0

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

Comments

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

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

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

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

https://libreofficehelp.com/count-cells-strings-numbers-countif-function-calc-example/

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 8,150 times

Last updated: Jun 21 '18