Tutoring spreadsheets, here’s the next level of complexity with the sumif() function: a separate test range.

In my March 9 post I brought up sumif(), pointing out that it will selectively add values within a given range.

sumif() offers one more level of flexibility: you can use a different test range from the sum range.

Example: Consider the following partial spreadsheet:

Let’s imagine that, above, 110 is in the A1 position.

The sumif() formula can be used with the following format:

=sumif(decision_range,condition,range_to_add)

Note that, in this case, the first parameter is the range to check for agreement with the condition, while the range of numbers to potentially add is the final parameter.

Continuing with the example, let’s imagine that the formula

=sumif(b1:b5,”red”,a1:a5)

is entered in cell c6. It will add only the cells adjacent to the value “red”, and return the value 90.

=sumif(decision_range,condition,range_to_add)

seems to work the same with Excel or LibreOffice Calc:)

Oracle Tutoring by Jack and Diane, Campbell River, BC.