Sum if Multiple Criteria
The following formula will be used in order to test the process of the sum if multiple criteria of values are present. This will sum all the values and present the result in given criteria specified for the purpose. The function in use here is SUMIFS along with the values and result criteria specified.
In an Excel worksheet, the SUMIFS is a function that is used to enter a part of the formula in a cell. Its main working is to do the summing of numbers in a range that has been based on the multiple criteria aspect. The basic syntax for COUNTIF function is:
The simple layout for the SUMIF function is as follows:
Figure 1: Syntax of SUMIFS function
The formula for sum if multiple criteria using the SUMIF function is:
The first three arguments are mandatory whereas the ranges and associated criteria are optional. Here sum_range states to one or more cells to be summed up and is required. Only cells with numbers are summed whereas blank and values with text are ignored, criteria_range1 is the first range of criteria to be associated and criteria1 is the first condition that must be met. Furthermore, the criteria_range2 and criteria2 are optional associations.
Working of Formula
In the layout the, the range of (C2: C9) is the first range that has been termed as the “sum range”. The criteria supplied in this range is shown as (range/criteria) and the criteria pair to be used first is (A2: A9/ “apples”), which means that the range of A2: A9 has been assigned the apple category or criteria.
The next pair for criteria is (B2: B9/ “Pete”). This states that all the cells in the following range must be associated with the criteria of Pete. The whole formula is displayed as:
=SUMIFS (C2: C9, A2: A9, “apples”, B2: B9, “Pete”)
For result display, all the cells in the sum range will be processed and displayed and if there the criteria is not passed then the process will not proceed