In this article, we’ll explore how to use the SUM and COUNTIF (or COUNTIFS) functions when dealing with multiple criteria in Excel.:

The COUNT function is commonly used to count values within a specified range.
However, when working with classified data in an Excel sheet, we often need to count data based on specific criteria or conditions. Fortunately, Excel provides the COUNTIF function to address this need.

Method 1 – Counting Two Types of Criteria Using SUM and COUNTIF Functions

Suppose you want to determine the number of products that are either available or sold out. Follow these steps:

Select a cell where you’d like to display the total count of available and sold-out items.
Enter the following formula into that cell:

=SUM(COUNTIF(E5:E15,"Available"),COUNTIF(E5:E15,"Sold Out"))

The COUNTIF function first counts the number of Available items.
It then counts the values of Sold Out items.
The SUM function adds up these counts to give you the total number of available or sold-out items.

By using this approach, you can efficiently count items based on two different criteria.

Method 2 – Counting Items Within a Price Range

Let’s say you want to find out how many smartphones fall within a specific price range (e.g., between $850 and $1250). Follow these steps.

Choose a cell where you’d like to display the result.
Enter the following formula into that cell:

=SUM(COUNTIF(D5:D15,">850"),-COUNTIF(D5:D15,">=1250"))

The first COUNTIF counts the number of products with prices greater than $850.
The second COUNTIF subtracts the count of products with prices greater than or equal to $1250.
The result will be the number of products falling within the specified price range.

Method 3 – Using Multiple Criteria for a Date Range

In this modified scenario, let’s replace the Price and Status columns with the Arrival Date of products. Suppose you want to find out how many products were brought to the stores between January 10th and February 7th. Follow these steps:

Choose a cell where you’d like to display the result.
Enter the following formula into that cell:

=SUM(COUNTIF(D5:D15,">1/10/2022"),-COUNTIF(D5:D15,">2/7/2022"))

- The COUNTIFS function counts the number of products whose arrival dates fall within the specified date range.

By applying these methods, you can effectively handle multiple criteria in Excel using SUM and COUNTIF (or COUNTIFS) functions.

Useful Alternatives: COUNTIF Family Function with SUM

When working with Excel, the COUNTIF function allows us to count values based on a single criterion. However, if we encounter scenarios with multiple criteria, using nested COUNTIF functions can lead to larger and less efficient formulas. To address this, we can turn to the COUNTIFS function, which allows us to specify multiple criteria for corresponding ranges. Let’s explore the applications of this function below to gain a better understanding.

Method 1 – Counting Two Types of Criteria Using COUNTIFS Functions

Suppose we want to achieve the same result as in Method 1 (which used SUM and COUNTIF) but with the COUNTIFS function. Follow these steps:

Select a cell where you’d like to display the total count of items that are either Available or Sold Out.
Enter the following formula into that cell and press ENTER:

=SUM(COUNTIFS(E5:E15,{"Available","Sold Out"}))

- In this formula, we insert the criteria (“Available” and “Sold Out”) as an array within the COUNTIFS function using the second set of curly brackets.
- Without the SUM function, the COUNTIFS would return the counts of Available and Sold Out items separately. However, by using SUM, we obtain the total count of both types combined.

By leveraging the SUM and COUNTIFS functions together, you can efficiently count items based on multiple criteria in your Excel worksheets.

Method 2 – Applying Multiple Criteria to One Product

Suppose we want to determine the number of Available or Sold Out Galaxy S20 smartphones in the shop. As our criteria become more complex, it’s beneficial to use the COUNTIFS function. Follow the steps below for a better understanding:

Select a cell where you’d like to store this information about the Galaxy S20.
Enter the following formula into that cell:

=SUM(COUNTIFS(C5:C15,"Galaxy S20",E5:E15,{"Available","Sold Out"}))

- In this formula:
  - We specify the product range (C5:C15).
  - The first criterion is set to Galaxy S20.
  - The second criterion includes both Available and Sold Out statuses.
- By using SUM, we obtain the total count of Galaxy S20 smartphones that meet either of these criteria.

By applying the SUM and COUNTIFS functions, you can efficiently count multiple criteria related to a single product.

Similar Readings

Method 3 – Applying SUM and COUNTIFS to Multiple Items’ Availability Criteria

In this method, we’ll explore how to use the SUM and COUNTIFS functions to handle multiple criteria for various products. Suppose we want to count how many iPhone 14 and Galaxy S20 smartphones are available across all stores, with a requirement of more than 9 in stock. Follow the steps below:

Select a cell where you’d like to display the result.
Enter the following formula into that cell:

=SUM(COUNTIFS(C5:C15,"iPhone 14",D5:D15,">9"),COUNTIFS(C5:C15,"Galaxy S20",D5:D15,">9"))

- In this formula:
  - We specify the product range (C5:C15) for both iPhone 14 and Galaxy S20.
  - The first COUNTIFS counts the number of available iPhone 14 smartphones with stock greater than 9 in Store 1 and Store 3.
  - The second COUNTIFS counts the number of available Galaxy S20 smartphones with stock greater than 9 across all stores.
  - The SUM function adds up these counts to give us the total count meeting the specified criteria.

By implementing SUM and COUNTIFS functions, you can efficiently handle multiple criteria for various products in your Excel worksheets.

Method 4 – Applying Availability and Price Range Criteria

Suppose we want to determine how many smartphone products are priced less than $900 and have either an Available or Coming Soon status. Follow the steps below:

Select a cell where you’d like to store the result.
Enter the following formula into that cell and press ENTER:

=SUM(COUNTIFS(E5:E15,"Available",D5:D15,"<900"),COUNTIFS(E5:E15,"Coming Soon",D5:D15,"<900"))

- In this formula:
  - We specify the product price range (D5:D15) for items priced below $900.
  - The first COUNTIFS counts the number of Available smartphones with prices less than $900.
  - The second COUNTIFS counts the number of Coming Soon smartphones with prices less than $900.
  - The SUM function adds up these counts to give us the total count meeting the specified criteria.

By applying the SUM and COUNTIFS functions, you can efficiently handle multiple criteria related to availability and price ranges in your Excel worksheets.

Practice Section

In this section, I’m providing you with the dataset from this article. You can use this dataset to practice the methods discussed and enhance your expertise.