So, you’re looking for a new spreadsheet function to add to your current spreadsheet? Well, there are plenty of options. However, when it comes to the function you might not be aware of, COUNTIF and COUNTIFS functions in Google Sheets are one of the most powerful ones you’ll ever find. Using COUNTIF and COUNTIFS, you can create formulas that count the number of cells with a specific value in them.
To get more bored about this topic, you can watch our full video tutorial below. However, if you already know how to use these functions and want to learn more about them, keep reading. You’ll be glad that you did!
RELATED: How to Use Excel’s IF function with VLOOKUP and HLOOKUP Functions
What are COUNTIF and COUNTIFS functions
The COUNTIF and COUNTIFS functions are complementary functions in Google Sheets that can be used to count how many cells have a specific value. The syntax for these functions is the same. For example, the following formula will count cells with the word “Sales” in them: =COUNTIF(Sheet1!A:A, “Sales”)
The basic syntax for using these functions is as follows:
FunctionName(Array) [, Formula] [, Range] [, Parameter1[, Parameter2,…]] [, Syntax] In this overview we will be looking at both the COUNTIF and COUNTIFS functions. You can find more information about each function in their respective sections. The COUNTIF
How to use the COUNTIF function
The COUNTIF function can be used to count cells in a range of numbers. If you input the formula =COUNTIF(A2:A10, B2:B10), it will count the number of cells in A2 that have the same value as the cell in B2. The formula =COUNTIF(B2:B10, C2:C10) will count the number of cells in B2 that have the same value as the cell in C2.
How to use the ISERROR function. The ISERROR function can be used to check whether a particular row or range of cells contains an error value. If you input the formula =ISERROR(A1), it will return TRUE if there is a blank value in cell A1 and FALSE otherwise.How to use the ISBLANK function. The ISBLANK function is used to evaluate whether the value in a particular cell is blank. If you input the formula =ISBLANK(A1), it will return TRUE if there is no value in cell A1 and FALSE otherwise. How to use the ISNONTEXT function. The ISNONTEXT function can be used to check whether text from a cell contains non-text values such as numbers or dates. If you input
How to use the COUNTIFS function
This function enables you to count the number of times one or more items in a range meets a certain criteria. To use this function, enter the criteria in the left-most box, put your search term(s) in the middle box, then indicate what type of data you are counting (ie: if you are counting cells that contain text, put text and then X) on the right-side box. Once your criteria is set, simply hit “enter” on your keyboard. The function will then return a number.
To count how many times something occurs in a range, enter 2 in the “type of data” box, and put your search terms in the remaining boxes. If you want to count all occurrences of numbers or text, put the text or number value into the “value” box. If you want to exclude some values from the count total, put an exclamation point (!) after that value.
Example 1: This example counts the numbers
Other uses for the countifs function
The countifs function can be used in Google Sheets when looking for the number of times a specific word appears in a text. This can be useful when looking up how many times the word “welcome” appears in a list, or when counting the number of bills in a stack. Counting items in a list
The countifs function, with the argument list , can be used to count the number of times an item is contained in a list. For example, if you have a list of four items, and you want to find out how many are present, you could use this formula. This would return 3 because there were three items in your list.
=countifs(list,”there are”)
Conclusion
Countif and countifs are helpful functions for counting cells that have specific values. They can help with making formulas to find the average number of values for a range in a spreadsheet. Comments? Suggestions? Corrections? Please use the contact form below. If you found this content useful and wanted to see more, consider purchasing one of my courses on Udemy! true