June 18, 2021

How to use sumifs in excel

The SUMIFS function is similar to the SUM function, the SUMIF function that I showed in the previous lessons is to calculate the total in Excel. But the SUMIFS function is a little bit superior, it’s the function that sums many conditions.

SUMIFS is a function to sum cells that meet multiple criteria. SUMIFS can be used to sum values when adjacent cells meet criteria based on dates, numbers, and text. SUMIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Syntax: = SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

  • Sum_range (required): The range of cells to sum.
  • Criteria_range1 (required): The range that is tested using Criteria1. Criteria_range1  and Criteria1 set up a search pair whereby a range is searched for specific criteria. Once items in the range are found, their corresponding values in Sum_range are added.
  • Criteria1 (required): The criteria that defines which cells in Criteria_range1 will be added. For example, criteria can be entered as 32, “>32”, B4, “apples”, or “32”.
  • Criteria_range2, criteria2, … (optional): Additional ranges and their associated criteria. You can enter up to 127 range/criteria pairs.

Example: To better understand how to use the SUMIFS function in Excel, we will go into the actual example as below to better understand how to use the SUMIFS function. We have the following Excel table

sumifs excel 1 - How to use sumifs in excel

1.1. Calculate the total SEP of LA with OCT with a value greater than 300

Syntax: =SUMIFS(C2:C9,B2:B9,”LA”,D2:D9,”>300″)


excel sumifs - How to use sumifs in excel

1.2. Calculate the total SEP of LA minus papay

Syntax: =SUMIFS(C2:C9,B2:B9,”LA”,A2:A9,”<>papay”)


sumifs function - How to use sumifs in excel

READ:  How to use the Excel FINDB function

Leave a Reply

Your email address will not be published. Required fields are marked *