How to reference cells with the COUNTIF function in Excel
-
Loading... - Cecile Sanchez
- 20 Feb 2023
- 82 Views
- 0 Like
- 0 Comment
Use COUNTIF to count values in a range that meet a certain condition and return a specified number to the cell.
Counting values is an easy task using Excel’s COUNTIF() function, which is essentially a combination of the COUNT() and IF() functions. It is used to count the number of values in a range that meet a specific condition. To make it work, you supply the range and a condition, which can be a literal value or an expression.
In this tutorial, I’ll show you how to effectively implement COUNTIF() using a simple sheet of sales and commission values. Specifically, we’ll compare the current year’s commissions to last year’s to see if anyone surpassed last year’s highest commission. This means using comparison operators, which requires a bit of specialized knowledge.
Before we get started, it’s important to know that what you’re learning about expressing conditions in an Excel-friendly way doesn’t only apply to COUNTIF(). You can use what you learn here to optimize a variety of functions and expressions.
For this demonstration, I’ll be using Microsoft 365 Desktop on a Windows 10 64-bit system, but you can use earlier versions of Excel as well. Excel for the web fully supports COUNTIF(). To follow along with our specific examples, you can download the demonstration file here.
Can the COUNTIF function reference a cell?
The simple answer is yes, COUNTIF() can be used to reference a cell. Similar to other functions, COUNTIF() can reference cells or ranges. However, the condition is altogether another thing. If the condition is in a cell, you’re fine, but that often won’t be the case. Before we tackle this problem, let’s look at COUNTIF()’s argument:
COUNTIF(range, criteria)
Here, “range” is the value or values you’re counting and “criteria” is the condition that determines whether or not the function includes a value in the count. This function requires both arguments.
The range argument can be numbers, arrays, a named range or a reference to a range that contains the values to be counted. It’s important to note that this function ignores blank and text values. Read More…
