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.

**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: ****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"))`

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.

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

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

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

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