How to Apply SUM and COUNTIF for Multiple Criteria in Excel

The article will show you how to apply SUM and COUNTIF or COUNTIFS functions when multiple criteria are involved. Basically, the COUNT function counts values within a range. But sometimes in an Excel sheet, users use classified data. So we need to count data based on criteria or a condition in that case. Fortunately, Excel has the COUNTIF function to remedy the situation. Let’s continue to the following sections of this article to see the combined application of the SUM and COUNTIF functions when there are multiple criteria in the dataset.


Download Practice Workbook


3 Ways to Apply SUM and COUNTIF for Multiple Criteria in Excel

In the dataset, we have some smartphone products with their names, prices, and availability statuses. Thus we divide them into multiple criteria. Let’s use the criteria to count them in some unique ways.

sum countif multiple criteria


1. Counting Two Types of Criteria by SUM and COUNTIF Functions

In this section, I’ll show you the simplest application of using SUM and COUNTIF functions to count multiple criteria at once. Suppose, you want to find out the number of products that are available or out of stock. Let’s follow the description below.

Steps:

  • First, select a cell to store the number of Available or Sold Out items and type the following formula in it. The formula will immediately show the values of the number of total Available and Sold Out

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

Counting Two Types of Criteria by SUM and COUNTIF Functions

Here, the COUNTIF function first counts the number of Available items, then it counts the values of Sold Out items, and finally, the SUM function adds up those values to return the number of Available or Sold Out items.

Thus you can apply SUM and COUNTIF functions to count two types of criteria.

Read More: Excel COUNTIF for Multiple Criteria with Different Column


2. Counting Items Within a Price Range

We can also use criteria for a specific price range. Suppose, we want to know how many smartphones have a price between 850 dollars and 1250 dollars. The following description will lead you to the solution.

Steps:

  • First, choose a cell to store the result.
  • After that, type the following formula in the cell and hit ENTER.

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

sum countif multiple criteria method 2

The formula will return the number of products having a price range between 850 dollars and 1250 dollars.

Thus, we can apply multiple criteria on a price range thus counting the number of products.

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


3. Using Multiple Criteria for a Date Range

In this section, we made some modifications. We replaced the Price and Status column with the product Arrival Date. We will find out how many products were brought to the stores from 10th January to 7th February.

Steps:

  • First, choose a cell to store the result.
  • Next, type the following formula in the cell and hit ENTER. The formula will show the total number of smartphones that the buyers brought to the store within the date period.

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

Using Multiple Criteria for a Date Range

Thus we can also apply the SUM and COUNTIF functions for a date range as multiple criteria.

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


Useful Alternatives: COUNTIF Family Function with SUM

As we can only use one criteria in the COUNTIF function, we may have to use nested COUNTIF functions when we face a lot of criteria. This will make the formula bigger and inefficient. To apply criteria conveniently, we can use the COUNTIFS function which can occupy multiple criteria for corresponding ranges. Let’s go through the applications of this function below to get a better understanding.


1. Counting Two Types of Criteria Using COUNTIFS Functions

We can also obtain the same output as we get in Method 1 by using the COUNTIFS function. Please go through the description below.

Steps:

  • First, select a cell to store the number of items that are Available or Sold Out.
  • After that, type the following formula in the cell and hit ENTER.

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

sum countif multiple criteria method 4

Here, we insert the criteria (“Available” and “Sold Out”) as an array in the COUNTIFS function using the second bracket. If we didn’t use the SUM function, it would return the number of Available and Sold Out items separately. But here, the SUM function will return the total number of Available and Sold Out items.

Thus you can count multiple criteria using the SUM and COUNTIFS functions.

Read More: INDEX, MATCH, and COUNTIF Functions with Multiple Criteria


2. Applying Multiple Criteria to One Product

Suppose, we want to know the number of Available or Sold Out Galaxy S20 smartphones in the shop. As our criteria become complex, it’s good to use the COUNTIFS function. Let’s go through the procedure below for a better understanding.

Steps:

  • First, specify a cell where you want to store this information about Galaxy S20.
  • Next, write down the formula below that will return our desired result.

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

Applying Multiple Criteria to One Product

The explanation for this formula is similar to the previous methods. We want to know about the Available or Sold Out Galaxy S20 smartphones, so we selected the Product range (C5:C15), and set the first criteria to Galaxy S20.

Thus you can apply SUM and COUNTIFS functions to count multiple criteria on one product.

Read More: How to Use COUNTIF with Multiple Criteria in the Same Column in Excel


3. Implementing SUM and COUNTIFS on Multiple Items Availability Criteria

The following process will show you how you can apply multiple criteria on multiple products. Suppose, we will count how many iPhone 14 and Galaxy S20 smartphones are available, more than 9 throughout all the stores. Please follow the instructions below.

Steps:

  • First, choose a cell to store the result.
  • After that, type the following formula in 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

Here, the number of available iPhone 14 in Store 1 and Store 3 is more than 9. The number of Available Galaxy S20 is more than 9 in all the stores. So the formula here returns 5.

Thus you can implement SUM and COUNTIFS functions to apply multiple criteria on multiple products.

Read More: SUMPRODUCT and COUNTIF Functions with Multiple Criteria


4. Applying Availability and Price Range Criteria

Suppose, we want to know how many smartphone products are priced less than 900 dollars with Available and Coming Soon statuses. Let’s have a glimpse of the following description.

Steps:

  • First, select a cell to store the number of items that have prices less than 900 and Available or Coming Soon.
  • After that, type the following formula in the cell and hit ENTER.

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

Applying Availability and Price Range Criteria

If you look at the Price and Status columns respectively, you can see that Store 1 and Store 2 both have 2 smartphones each that have prices below 900 dollars while Store 3 has one item that meets the condition. Also, these products are either Available or Coming Soon. So we have 5 as the formula output.

Thus we can apply SUM and COUNTIF functions for multiple criteria.

Read More: How to Use Excel COUNTIF That Does Not Contain Multiple Criteria


Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own and gain expertise.


Conclusion

In the end, we can consider that you will have a clear idea of how to apply SUM and COUNTIF functions for multiple criteria in an Excel dataset. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo