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.
How to Apply SUM and COUNTIF for Multiple Criteria in Excel: 3 Simple Ways
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.
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"))
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: COUNTIF Between Two Values with Multiple Criteria in Excel
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"))
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"))
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"}))
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.
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"}))
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.
Similar Readings
- How to Use COUNTIF with Multiple Criteria in the Same Column in Excel
- How to Use COUNTIF for Cells Not Equal to Text or Blank in Excel
- SUMPRODUCT and COUNTIF Functions with Multiple Criteria
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"))
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: How to Apply COUNTIF with Multiple OR Criteria in Excel
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"))
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.
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.
Download Practice Workbook
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.
Related Articles
- How to Use COUNTIF Between Two Dates and Matching Criteria in Excel
- How to Use COUNTIF Function Across Multiple Sheets in Excel
<< Go Back to COUNTIF Multiple Criteria | Excel COUNTIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
