**SUMIF** and **AND** are the most frequent functions in everyday life when we work in Microsoft Excel. If you are an Excel enthusiast, then you might already work on those functions separately. However, you can employ both functions in your formula editor to accomplish your task if needed. In this article, you will learn two specific examples of how to use** SUMIF** and **AND **function in Excel.

## How to Use SUMIF and AND Function Together in Excel: 2 Examples

Let’s assume we have a dataset, namely **“List of Customers at ABC Super Shop in Year 2022-23”. **You can use any dataset suitable for you.

Here, we have used the **Microsoft Excel 365 **version; you may use any other version according to your convenience.

### Example 01: Application of SUMIF & AND Functions with Multiple Criteria

Here, in our first example, we will look for “**Online**” transacted purchase values and then sum those **values using the SUMIF function**. Later, we will make a decision on whether those accumulated purchase amounts meet the targeted value by incorporating **the AND function** within **the SUMIF function **simultaneously. So stick with us to learn the process.

📌 ** Steps**:

- In cell
**C15**, write the following formula to calculate the accumulated value of the transaction through the “**Online**” medium.

`=SUMIF(D5:D13,"Online",C5:C13)`

**Formula Breakdown:**

**SUMIF(D5:D13,”Online”,C5:C13) →**Given**SUMIF function**adds the cells specified by a given criteria or condition. Here,**D5:D13**is theargument that refers to the*range*. Then, the string*Medium of Payment***“Online”**refers to theargument to apply within the given range. Lastly,*criteria***C5:C13**is the optionalargument, which indicates the values to sum within the range.*sum_range***Output → 2640**

- Press
**ENTER**to see the output later.

- Now in cell
**E15**, write the following formula to get a decision on whether the total amount has met the requirement.

`=AND(SUMIF(D5:D13,"Online",C5:C13)>D17,`

`SUMIF(C5:C13,"Online",D5:D13)<D16)`

**Formula Breakdown:**

**AND(SUMIF(D5:D13,”Online”,C5:C13)>D17,SUMIF(C5:C13,”Online”,D5:D13)<D16) →**checks whether all the arguments are**TRUE,**and returns**TRUE**if all the arguments are**TRUE**. Here,**SUMIF(D5:D13,”Online”,C5:C13)>D17**is theargument that checks if the date in the*logical1***D17**cell is less than the total amount of transaction through Online, and**SUMIF(C5:C13,”Online”,D5:D13)<D16**is theargument that checks whether the date in the*logical2***D16**cell is greater than the total amount of transaction through Online as well. Now, since both arguments return**TRUE**thus**AND**function returns the output**TRUE**.**Output → TRUE**

- Press
**ENTER**to see the output as depicted below.

### Example 02: Application of SUMIF & AND Functions with Dates

Though the problem we have selected as our second example involves multiple criteria, as in **Example 1**, we have selected a specific date as criteria 1 in this example to calculate the purchase amount of our given dataset.

📌 ** Steps**:

- Write the following formula in cell

**=SUMIF(E5:E13,”>=1/1/2023″,C5:C13)**

**Formula Breakdown:**

**SUMIF(E5:E13,”>=1/1/2023″,C5:C13)→**Given**SUMIF function**adds the cells specified by a given criteria or condition. Here,**E5:E13**is theargument that refers to the*range**Date*of the given dataset. Then, the string**“>=1/1/2023”**refers to theargument to apply within the given range. Lastly,*criteria***C5:C13**is the optionalargument, which indicates the values to sum within the range.*sum_range***Output → 2490**

- Press
**ENTER**to see the output as given below.

- Next, type the following formula in cell
**E15**.

` =AND(SUMIF(E5:E13,">=8/1/2021",C5:C13)>D17,SUMIF(E5:E13,">=8/1/2021",C5:C13)<D16)`

**Formula Breakdown:**

**AND(SUMIF(E5:E13,”>=8/1/2021″,C5:C13)>D17,SUMIF(E5:E13,”>=8/1/2021″,C5:C13)<D16) →**checks whether all the arguments are**TRUE,**and returns**TRUE**if all the arguments are**TRUE**, returns**FALSE**Here,**SUMIF(E5:E13,”>=8/1/2021″,C5:C13)>D17**is theargument that checks if the date in the*logical1***D17**cell is less than the total amount of transaction through Online, and**SUMIF(E5:E13,”>=8/1/2021″,C5:C13)<D16)**is theargument that checks whether the date in the*logical2***D16**cell is greater than the total amount of transaction through Online as well. Now, since the first argument returns**FALSE**while the second argument returns**TRUE**thus**AND**function returns the output as**FALSE**.**Output → FALSE**

- See the output after pressing on
**ENTER**.

**Read More: Nested IF and AND Functions in Excel**

## Things to Remember

**AND function**is able to take up to**255**arguments as its logical condition.- If you provide an array condition into the
**SUMIFS****function**which simultaneously discovers a merged cell as the return destination, an**#SPILL error**will occur.

## Practice Section

We have provided a** Practice** section on the right side of each sheet, so you can practice yourself. Please make sure to do it yourself.

**Download Practice Workbook**

You can download and practice the dataset that we have used to prepare this article.

## Conclusion

In this article, we have discussed **how to use SUMIF and AND function in Excel**. Practice by downloading the dataset to make yourself proficient in it. Further, if you have any queries, feel free to comment below, and we will get back to you soon.