Question

How to use the COUNTIF function of Google Sheets

Answer 1

COUNTIF on Google Sheets: Step-by-Step Instructions

---

How to use the COUNTIF function of Google Sheets

The COUNTIF function, also called COUNTIF in Spanish, is not only found in Excel: it is a very common solution in spreadsheets to count different values based on one or more conditions. The web-based office application Google Sheets also includes this handy feature..

The COUNTIF formula of Google Sheets is very useful, for example, when you want to add date values? In this case, the condition can also be in text format , allowing you to count the times the same name appears in a list or the same product in a general inventory . In accounting , the COUNTIF function of Google Sheets is used to compile the records for the last few months. In this way, you can quickly get an overview of the amounts and invoices received.

The formula to conditionally count values ​​in Google Sheets is as follows:

 

  =CONTAR.SI(intervalo;criterio)  

  =COUNTIF(intervalo;criterio)  

COUNTIF on Google Sheets: Step-by-Step Instructions

If you have a table where you want to add different values ​​that meet one or more conditions, the COUNTIF function is the perfect option. Once you have this formula ready to use in Google Sheets, the rest of the process will take a matter of minutes. You just have to follow these steps:

1. First, open a new or existing Google Sheets spreadsheet.

Google Sheets - Blank Sheet

2. If the table doesn't include any values ​​yet, enter them now:

Sample data in a Google Sheets sheet

3. Now you can write the COUNTIF function by hand in the cell you want or enter it from the menu. In the second case, open the tab? Insert ?, click? Function ? and hover over the option? Mathematics ?. In the drop-down menu, select? COUNTIF ? or? COUNTIF ?:

Insert COUNTIF in Google Sheets from menu

4. Click on the formula to insert it into the selected cell:

Google spreadsheet example with COUNTIF in a cell

5. Suppose you want to see the number of invoices with an amount less than 6000 . Click on the cell that should include the formula and enter the number of the cell where the sum should begin in parentheses . In our example, the range begins in cell? B2 ?. Enter a colon and write the number of the cell in which the sum must end , which in our case would be? B5 ?:

Google Sheets: selected values ​​for COUNTIF

6. You have just defined the range of the sum for the formula, but not exactly what should be counted. Since you want to know how many values ​​in the table have an amount less than 6000, you must define this condition in the formula . First, remove the closing parenthesis from the formula range and enter a semicolon . Then enclose <6000 in quotes and reinsert the closing parenthesis:

Google Sheets: COUNTIF function with a condition

7. Then press the return key for the program to count the results. The number of invoices with an amount less than 6000 will be displayed. In the example table, there are exactly two records that follow that condition ( 3000 and 4000 ):

Google Sheets: COUNTIF function result

Of course, you can also search for the number of invoices with an amount greater than 6000 by entering > instead of < . Also, with the COUNTIF function of Google Sheets you can use the = character and even define two conditions . For example, if the table contains other cells with a value greater than 12,000, you can enter:

  =COUNTIF(B2:B500;"<6000";B2:B500;">=12000")  

This formula shows you how many invoices are below 6,000 and how many have an amount of 12,000 or more..

If you have noted the date of receipt of the invoice in another row and, for example, you want to know how many invoices were received before 01/31/2020, you can create the corresponding formula with this structure:

  =COUNTIF(B2:B5;"<31/1/2020")  

Because multiple users can use Google Sheets at the same time , it is possible for multiple employees from different departments to edit the same table, depending on their tasks. For example, the accounting department may want to know how many invoices are still unpaid this month, while the marketing department can use the COUNTIF function of Google Sheets to see how often a type of customer has purchased certain products.

---