This tutorial will explain various ways of COUNTIF function in LibreOffice Calc can be used to count strings, numbers from a cell, range, rows, columns etc.
Table of Contents
COUNTIF Function – Syntax
COUNTIFS(Range1; Criterion1 [; Range2; Criterion2 [; ...]])
Returns the count of rows or columns that meet the criteria.
Count Numbers
With Number in Formula
The below example returns the count of 20 in the range A1:A7. In this below example, number 20 is present twice in the range. Thus the result is 2.
=COUNTIF(A1:A7,20)
With Number as Cell Reference
You can also specify 20 as a cell reference. The result would be similar. COUNTIF would try to count the value specified in cell D1.
=COUNTIF(A1:A7,D1)
Count Numbers with Operators
COUNTIF also can count using logical operators. It can count the number of rows and columns which satisfy the greater than and less than conditions.
Greater Than
In the above example, if we need to count the number of rows with values greater than 20, use the formula below. Note that the greater than operator should be used as a string (i.e., double quotes).
=COUNTIF(A1:A7,">"&D1)
Without cell reference, you can use COUNTIF(A1:A7,">20") as well, which would give the same result.
Less Than
Use the < sign to find out the number of rows less than the specified value.
=COUNTIF(A1:A7,"<"&D1)
Not Equal To
Similarly, use the <> sign to find out the number of rows not equal to the specified value.
=COUNTIF(A1:A7,"<>"&D1)
Equal, Greater Than Equal, Less Than Equal
You can use =, >=, and <= in the above formulas to get the results for Equal, Greater Than Equal and Less Than Equal to a specified value.
COUNTIF Using OR
You can also combine two COUNTIF functions to achieve more. The below example returns the count of rows which is 20 or 10.
=COUNTIF(A1:A7,"="&D1)+COUNTIF(A1:A7,"="&E1)
Using another function inside COUNTIF
It is possible to use another Calc function in the criteria of COUNTIF. The following example returns the values greater than the average of A1:A7, i.e. 32.7
=COUNTIF(A1:A7,">"&AVERAGE(A1:A7))
Count String or Text
COUNTIF can be used to count any text, or string as well in Calc cells like the numbers. The formula is exactly the same. Only the criteria should be a text/string specified by double quotes.
Count a specific text
The following example counts the text “apple” in the range A1:A10.
=COUNTIF(A:A10,D1)
Count all text
The above example, A1:A10, has a mix of values – numbers and texts. If you want to count the rows with texts only, the formula below can be used.
=COUNTIF(A1:A10,"*")-COUNT(A1:A10)
* gives the count of all the cells, including numeric and text. The COUNT() function gives the count of only numeric cells.
Tip:
- Use the criteria below to count all the cells with text starting with any text (e.g. “apple”).
=COUNTIF(A1:A10,"apple*")
- Use the criteria below to count all the cells with text containing any matching string (e.g. “apple”).
=COUNTIF(A1:A10,"*apple*")
- To count the cells with matching string with any one character at the end, use the below criteria.
=COUNTIF(A1:A10,"apple?")
So, this is how you can use COUNTIF for various uses in LibreOffice Calc.
Official Reference
Do let me know in the comment box if you have any questions.
I have a text document with a table and I would like to count number of cells in a column with a specific value. The basic form =COUNTIF(A1:A7,20) will do. IIUC, in a text document I must refer to the cell range with |, but this creates an error. Since my LibreOffice is not in English, must I use a translated name for the function? Or is COUNTIF available in a text document table at all?
I have a text document with a table and I would like to count number of cells in a column with a specific value. The basic form =COUNTIF(A1:A7,20) will do. IIUC, in a text document I must refer to the cell range with |, but this creates an error. Since my LibreOffice is not in English, must I use a translated name for the function? Or is COUNTIF available in a text document table at all?
In my LibreOffice 5.1.6.2 Version the right syntax is different!
To count all Cells in Column J with an email address (containing the @ symbol), I have to write:
=COUNTIF(J1:J3354;".*@.*")So
;not,and.*regular expressionNot
=COUNTIF(J1:J3354,"*@*")as written above!The , needs to be a ; according to my Libre office. Version 7.0.4.2
To Count all cells with text in a range the instructions above seem to indicate
=COUNTIF(A1:A10,”*”)
is correct however it didn’t work for me but
=COUNTIF(A1:A10;”*”)
did work
[semicolon not comma]
Yes, it depends on the regional settings and the options. Default USA regional uses comma where as German and other uses semicolon (;). We will have a separate article for it.