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

Consider the following dataset with some order information. We’ll use SUMIF with AND to comb through the data.

How to Use SUMIF and AND Function in Excel

Example 1 – Application of SUMIF and AND Functions with Multiple Criteria

We will look for Online purchases, sum those values, then determine if they are above a threshold.


  • In cell C15, use the following formula to calculate the accumulated value of Online transactions (column D).

Formula Breakdown:

  • SUMIF(D5:D13,”Online”,C5:C13) → The 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. The string “Online” refers to the criteria argument to apply within the given range. In this case, the formula will check if the value is equal to the criteria. C5:C13 is the optional sum_range argument, which indicates the corresponding value to sum if the value from range satisfies the criteria.
  • Hit Enter.

Application of SUMIF and AND Function with multiple criteria

  • Insert the sum benchmarks in cells D17 and D16 (lower and upper bound).
  • In cell E15, use the following formula determine whether the total amount has met the requirement.

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. SUMIF(D5:D13,”Online”,C5:C13)>D17 checks if the value in the D17 cell is less than the total amount of transaction through Online, and SUMIF(C5:C13,”Online”,D5:D13)<D16 checks whether the value in the D16 cell is greater than the total amount.
  • Since both arguments are TRUE, the AND function returns the output TRUE.
  • Press Enter.

How to Use SUMIF and AND Function in Excel

Example 2 – Application of SUMIF and AND Functions with Dates


  • Use the following formula in cell C15 to sum all the sales made on or after December 1, 2023.

Formula Breakdown:

  • SUMIF(E5:E13,”>=1/1/2023″,C5:C13)→ The string “>=1/1/2023” refers to the criteria argument to apply within the given range. It checks whether each cell in the range E5:E13 has a value higher than the date of 1/1/2023, then sums up the corresponding value from C5:C13.
  • Press Enter.

Application of SUMIF & AND Functions with Dates

  • Insert the value benchmarks in D16 and D17 (upper and lower bound).
  • Use the following formula in cell E15.

Formula Breakdown:

  • AND(SUMIF(E5:E13,”>=8/1/2021″,C5:C13)>D17,SUMIF(E5:E13,”>=8/1/2021″,C5:C13)<D16) 
  • The SUMIF functions calculate the sum of values after August 1, 2021.
  • AND compares the result against the benchmarks and returns the output TRUE if the value is between D16 and D17 (done by comparing SUM>D16 AND SUM<D17).
  • Hit Enter.

sumif and and function excel

Read More: Nested IF and AND Functions in Excel

Things to Remember

  • The AND function can 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, a #SPILL error will occur.

Practice Section

We have provided a Practice section on the right side of each sheet to test these methods.

Do It Yourself

Download the Practice Workbook

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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