Nesting formulas, functions and conditional statements in the SUMIFS criteria

We’ve previously established what are the rules of writing criteria in the SUMIFS function:

  • we can reference any cell
  • we can enter any number directly in the function
  • we can enter text directly in the function, nested inside of quotation marks [“”]
  • logical test is nested inside of quotation marks [“”], with comparison operator coming first and a number second.

The story however does not end there, and advanced users can do even more with the SUMIFS function.

 

We can nest formulas in the SUMIFS function criteria with the help of the parenthesis [()].

Consider the following example:

sumifs criteria formula in parenthesis

We have a list of numbers from 1 to 100, and we want to SUM the numbers that are equal to 6. Instead of just writing 6 in the criteria, we can also nest formula between parenthesis. The SUMIFS function is returning the sum of all the numbers in the column A equal to 6 – just one in this example.

 

Another thing we can do is to nest a function in the criteria:

sumifs criteria is function

In this example, the SUMIFS function is returning the sum of all the numbers in the column A equal to the smallest number, 1.

 

These principles become obviously useful when combined with conditional statements.

Logical test is usually nested inside of quotation marks [“”], with comparison operator coming first and a number second. However, this will not work with formulas and functions as they will be treated as text and not calculated.

In order to combine logical test with formulas and functions we first have to nest logical operator inside of quotation marks [“”], use ampersand [&], and only then write our function or formula.

 

We could write a formula that will sum only numbers smaller than 6 in this way:

sumifs criteria less than and formula

We would return 15, as that is the sum of numbers smaller then 6, cells A1 to A5.

 

We could write a formula that will sum only numbers smaller than average in this way:

sumifs criteria less than and average function

We would return 1275, as our average is 50,5 and with the given criteria, our formula will only sum the numbers smaller than that number, cells A1 to A50, returning 1275.

 

What if we want to only sum the numbers below the 10th percentile – i.e. 10% of the lowest values?

We would use this formula:

=SUMIFS(A:A;A:A;”<“&PERCENTILE.INC(A:A;0,1))

What if we want to only sum the numbers between the 10th percentile and 20th percentile?

We could sum the values below the 20th percentile and subtract the sum of the values below the 10th percentile, like this:

=SUMIFS(A:A;A:A;”<“&PERCENTILE.INC(A:A;0,2))-SUMIFS(A:A;A:A;”<“&PERCENTILE.INC(A:A;0,1))

This is how the returns would look like:

sumifs criteria less than percentile

 

Naturally, if we want to sum the numbers above a certain average or percentile, we can use greater than [>] operator.

These are Comparison operators available, and can all be nested in the SUMIFS criteria:

Operator Description
= equal to
> greater than
>= greater than or equal to
< less than
<= less than or equal to
<> not equal to

 

Dig deeper:

Conditional calculations

Leave a Reply

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