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

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.

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.

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.

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