The SUMIFS function

The SUMIFS function is a math and trigonometry function which combines the benefits of the SUM function and the IF function. It allows you to take a range of cells and add them together only if they meet all of the listed criteria. Criteria do not need to be a part of the initial range that is being added together.

This is the format of the SUMIFS function:

=SUMIFS(SUM RANGE, CRITERIA RANGE 1, CRITERIA 1, CRITERIA RANGE 2, CRITERIA 2, etc…)

Here is an example of the SUMIFS function:

=SUMIFS(C2:C6, A2:A6, “A”, B2:B6, “Fixed”) 

SUMIFS returning the sum of all cells within the initial range whose criteria in the subsequent ranges has been met

This function is valuable when you are organising or creating reporting structures that rely on source data from an external application. This data is almost always organised in a table format, which makes it as simple a process as possible with SUMIFS.

An example of a functional implementation of this would be totalling sales data by region to gain a better understand of how your product mix can target customers within those regions. Regions are separated by province, and each customer has their province noted on their orders. By asking Excel to total order values by a specific province we can begin the process of analysing our results.

Ex: Column D has order values, and column H has province. All data starts on row 2.

=SUMIFS(D2:D20, H2:H20, “QC”)

=SUMIFS(D2:D20, H2:H20, “ON”)

=SUMIFS(D2:D20, H2:H20, “BC”)