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
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″)
Result:
1.2. Calculate the total SEP of LA minus papay
Syntax: =SUMIFS(C2:C9,B2:B9,”LA”,A2:A9,”<>papay”)
Result:
