How to Use SUMIF and AND Function in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Use SUMIF and AND Function in Excel

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.

Application of SUMIF and AND Function with multiple criteria

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

How to Use SUMIF and AND Function in Excel


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 C15.
 =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 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.

Application of SUMIF & AND Functions with Dates

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

sumif and and function excel

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.

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.


Related Articles


<< Go Back to Excel AND Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mohammad Shah Miran
Mohammad Shah Miran

Mohammad Shah Miran has a professional background spanning over a year at Softeko. Initially starting as an Excel and VBA writer, he authored more than 50 articles for the ExcelDemy project. Currently, Miran is engaged in the LinuxSimply project as a Linux content developer, completed over 40 articles. His analytical approach extends across various domains, including Excel, VBA, Bash scripting, Linux, data analysis, and Python programming. In his leisure time, Miran enjoys watching movies and series or listening... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo