Mohammad Shah Miran
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 the range argument that refers to the Medium of Payment. Then, the string “Online” refers to the criteria argument to apply within the given range. Lastly, C5:C13 is the optional sum_range argument, which indicates the values to sum within the 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 the logical1 argument that checks if the date in the D17 cell is less than the total amount of transaction through Online, and SUMIF(C5:C13,”Online”,D5:D13)<D16 is the logical2 argument that checks whether the date in the 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
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 the range argument that refers to the Date of the given dataset. Then, the string “>=1/1/2023” refers to the criteria argument to apply within the given range. Lastly, C5:C13 is the optional sum_range argument, which indicates the values to sum within the 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 the logical1 argument that checks if the date in the D17 cell is less than the total amount of transaction through Online, and SUMIF(E5:E13,”>=8/1/2021″,C5:C13)<D16) is the logical2 argument that checks whether the date in the 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.
