COUNTIF and COUNTIFS Functions

COUNTIF and COUNTIFS shine as two remarkable tools that combine the might of counting with the conditional logic of IF/S. In this article, we will not only explore these functions but also dissect their underlying mechanisms by breaking down the COUNT and IF/S components. So, let’s embark on this journey of understanding how COUNTIF and COUNTIFS work and how they harness the power of count + IF/S to simplify complex data analysis.

Before diving into COUNTIF and COUNTIFS, we will have a quick look at these COUNT and IF functions individually. This would help us in understanding the combined formula ahead.

COUNT: Counting Made Simple

At its core, the COUNT function in Excel is designed to count the number of cells within a given range that contain numeric values. There is also another variation, COUNTA, which counts all the cells containing various types of data, including numeric, text, date/time, or any data in general.

• COUNT Function: Simply counts the selected range that contains numbers.

=COUNT(range)

• COUNTA Function: Simply counts the selected range that contains numbers, text, special characters, or, in other words, it counts if the cell is not empty.

=COUNTA(range)

• range refers to the range of cells you want to count.

Example:

Here in this given example, we have tried to count the number of students by counting the cells in column A (Name) using Count and CountA functions.

The Brilliance of IF/S: Conditional Logic at Work

Now, let’s turn our attention to the IF/S component. The IF/S logic involves applying a condition or criterion to a dataset and performing an action based on whether that condition is met. In Excel, this is achieved using the IF function and its extended version, IF(S). Here’s a glimpse of what IF/S does:

• IF Function: Evaluates a condition and returns one value if the condition is true and another if it’s false.

=IF(logical_test, value_if_true, value_if_false)

• IFS Function: Evaluates multiple conditions and returns one value based on the true conditions that are true and another if it’s false.

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

• logical_test1 is the condition you want to evaluate.
• value_if_true1 is the result if the condition is true.
• logical_test2 is the condition you want to evaluate.
• value_if_true2 is the result if the condition is true.

Example: If and Ifs Function

• In the above example, we can see that the IF formula is applied in cell H7 (Syntax displayed in G7) where we checked the condition that if in cell C7, Marks are greater than or equal to 80, then mention Pass; otherwise, mention Fail (the same formula is dragged below in H8). 
• Then, for IFs formula, we checked that if the subject is Science, then mention On Hold. If this condition is not true, then check if marks are more than or equal to 80, then Grade A. 
• If even this condition is not true, then check if marks are greater than 60, then Grade B. Even if this condition is not true, then check if marks are less than 60, then Grade C.
  • Here we found that the result is On Hold because the subject was Science. 
  • In the next row, we can see that the result is Grade C because the subject is not Science, and marks are less than 60.

COUNTIF and COUNTIFS: The Confluence of Count and IF/S

Now, let’s dive into COUNTIF and COUNTIFS functions, which represent a harmonious blend of two fundamental concepts: counting and conditional logic. These functions seamlessly merge the counting capabilities of COUNT with the conditional decision-making prowess of IF/S, creating powerful tools for data analysis. Let’s dissect these functions to understand how they work together to simplify complex data evaluation.

COUNTIF: Counting with a Single Condition

COUNTIF is like a precision instrument for counting cells based on a single, specific condition. It allows you to count only those cells within a specified range that meet the given criterion. Here’s the breakdown:

=COUNTIF(range, criteria)

• range refers to the range of cells you want to evaluate.
• criteria is the condition you want to apply to each cell in the range.

Example: Suppose you have a list of test marks in a range, and you want to count how many scores are greater than or equal to 80. You can use COUNTIF like this:

Formula:

=COUNTIF(C2:C16, ">=80")

In this scenario, COUNTIF scans through the range C2 to C16 and counts the number of cells that satisfy the condition (scores greater than or equal to 80).

Note: Unlike COUNT function, COUNTIF counts all cells that match the given criteria irrespective of text or numbers.

COUNTIFS: Counting with Multiple Conditions

When your data analysis requirements involve multiple conditions that need to be met simultaneously, COUNTIFS steps in. It extends the counting power to include multiple criteria, ensuring that only cells meeting all specified conditions are counted. Here’s the essence of COUNTIFS:

=COUNTIFS(range1, criteria1, [range2, criteria2], ...)

• range1, criteria1 represent the first range and its corresponding criterion.
• [range2, criteria2], ... are optional additional ranges and criteria.

Example: Suppose you need to count how many subjects each student has passed. You can use COUNTIFS like this:

Formula:

=COUNTIFS(A2:A16, J20, C2:C16, ">=80")

This formula counts the rows where “John Smith” appears in the Name column (column A) and “Marks” is greater than or equal to 80 in the Marks column (column C). It provides an efficient way to determine how many subjects John Smith has passed based on the given criteria.