Excel formulas can really make a difference in spreadsheets and help us manage numbers automatically and easily, saving a lot of time with the calculator and accounts. In this article we see some advanced counting formulas in Excel and in LibreOffice Calc (even if everything is much simpler on Excel), which can be used in any spreadsheet, as long as you want, made of numbers or values also arranged in several columns.
READ ALSO: 4 operations to be able to do by working with Word and Excel
COUNTS
The COUNT function is used to count the number of cells in a range.
Accounting only works to count the number of cells containing numbers, dates, logical operators or text representing a number.
In LibreOffice this function is called COUNT . NUMBERS .
If you also want to include cells with text in the count, you use the COUNTA function, while to count the empty cells in a range, you can use COUNTA . EMPTY .
For example, you can count the number of cells with numbers by writing the function
= COUNT (A1: F2)
The range of cells to be counted is written in brackets.
COUNTIF is similar to the normal, but conditional, COUNT, where you can set a selection criterion for the cell count.
For example, in a list of names you can count how many are called Claudio by writing:
= CONTA.SE (A1: A100; "claudio") or = CONTA.SE (A: A; "claudio")
You can also use this method, for example, to find out how many students have scored more than 24, writing "> 24" as a selection criterion.
It is also possible, in Excel, to use a cell as a counting criterion, for example = COUNTIF (A: A; "B1") with in B1 a word that you want to count in column A.
COUNTIFS
While CONTASE allows you to count base cells of a single criterion, COUNTIF can be used when there are multiple conditions or criteria to be used in the count, in multiple ranges.
The syntax is as follows: = COUNTIF.SE (Cell-range, "criterion1", Interval2, "criterion2")
In LibreOffice you have to use the; instead of the, .
READ ALSO: 6 most useful Excel functions to filter data and see graphs
SOMMA.SE
The Sum is the function to sum the numbers in the selected cells, putting a condition.
The SOMMA.SE is the sum, with a selection criterion, to count not all the values in the range but only those that meet a certain criterion.
For example, having a table with two columns, one with the names and the other with the money earned with various activities, we can write:
= SUM (A: A, "Claudio", B: B) to add the money earned (taken from column B) by Claudio (extracted from column A).
Like the other formulas, in LibreOFfice you have to use the; and it's a little less easy to extricate yourself.
SUMIFS
If we add a third column to the example above, for example with people's cities of residence, we can write: = SUMIFS (B: B, A: A, "Claudio", C: C, "ROMA")
In this case, therefore, the function is added to the city where the money was earned by Claudio (imagining that Claudio worked in several cities earning different figures).
READ ALSO: How to make calculations on Excel
The SUM function is certainly more familiar than the COUNT function which is instead very useful for making statistics and counts in very large tables.
With SEs and PIUs selection criteria can be easily added to extract data from the tables.
There are also other formulas related to SUM, which can be used through the wizard.
Formulas can also be combined with logical functions to create conditional formulas (see here)
In these web pages further guides for functions and formulas on Excel.
READ ALSO: Getting good with Excel on spreadsheets (also with LibreOffice and Google Drive)
