How to Apply SUM and COUNTIF for Multiple Criteria in Excel (3 Simple Methods)

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:

sum countif multiple criteria

  • 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"))

Counting Two Types of Criteria by SUM and COUNTIF Functions

  • 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.

Read More: COUNTIF Between Two Values with Multiple Criteria in Excel


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"))

sum countif multiple criteria method 2

  • 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.

Read More: COUNTIF with Multiple Criteria in Different Columns in Excel


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.

Using Multiple Criteria for a Date Range

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

Read More: Excel COUNTIF Function with Multiple Criteria & Date Range


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"}))

sum countif multiple criteria method 4

    • 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"}))

Applying Multiple Criteria to One Product

    • 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"))

sum countif multiple criteria method 6

    • 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.

Read More: How to Apply COUNTIF with Multiple OR Criteria in Excel


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"))

Applying Availability and Price Range Criteria

    • 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.


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to COUNTIF Multiple Criteria | Excel COUNTIF Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo