< Go Back

Sum if multiple criteria

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:

 =SUMIF(sum_range,criteria_range1,criteria1,[criteria_range2, criteria2],…)


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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment