How to use SUM function across multiple rows matching several criteria in LibreOffice Calc Spreadhsheets (SUMIFS)

Written by Claudio Kuenzler - 0 comments

Published on March 8th 2021 - Listed in Personal Cloud Internet Office

---

In all Spreadsheet applications (Microsoft Excel, Google Sheets, LibreOffice Calc, ...) the SUM function is probably the widest used function. It allows to quickly show the total amount of all fields in a range.

But what if a field's value should only be part of that total sum if another criteria is matched? Or even if several criteria need to be matched before calculating a summarized total? This is what this article is about.

The SUMIF function

For example let's look at the following table:

Date	Person	Amount USD
2020-01-28	Sylvester Stallone	300
2020-02-14	Sylvester Stallone	140
2020-02-17	Arnold Schwarzenegger	160

We have multiple data to handle here. The third column represents some amount. This is obviously the relevant part which should be used in the SUM. But what if I want to find about the total amount our dear friend Sylvester spent? Using a SUM across the third column would also include Arnold's expenses and would not correctly represent the total sum.

In this case, the function SUMIF can be used. This function still uses the SUM of a range (third column in our example) but only IF a criteria in another range is matching. Looking at that table, our criteria range is obviously the second column and we're looking for the field value to be "Sylvester Stallone". The Calc Function Wizard is a very good helper:

After a click on Next, the following data must be selected and entered:

• range: In which field range do we apply our criteria (condition)? As we need to check for a specific name, which happens to be in column B, this would be range B2:B4 (from field B2 until including B4).
• criteria: We are looking for the field in the range to match "Sylvester Stallone"
• sum_range: Which range should be used for the SUM calculation? The Amount USD column is what we are looking for, so this is range C2:C4.
  

After a click on OK, the formula is inserted into the spreadsheet. And voilà, we got the total SUM matching only Sylvester's expenses. The same formula was used one field below, adjusted to find Arnold's expenses, too:

As one can see in the Function Wizard, there is only one criteria possible. But what if the data is more complicated and multiple conditions must be matched? This is where SUMIFS comes into play.

SUMIF with multiple conditions/criteria: SUMIFS

To handle more complicated data, for example with additional categories, one single criteria won't be enough. This is where the function SUMIFS helps out. It is basically the same as SUMIF but just expands for more criteria ranges.

Let's take a look at our cast's expense sheet (which is organized in a separate tab "Details"):

Compared to the previous example, this expense sheet contains much more data. And we can use this data to not only find out how much expenses each person caused, but we can categorize them, too (look at column D / Type).

For this to work, multiple criteria must be added into the formula and each criteria has its own range. The Function Wizard is also helpful here:

The following formula data must be entered:

• sum_range: Which range should be used for the actual SUM calculation? The Amount USD column is what we are looking for, so this is range E2:E14. 
  
• range 1: Specified which range to be used for condition / criteria 1
• criteria 1: Which condition should be matched in range 1?
• range 2: Specified which range to be used for condition / criteria 2
• criteria 2: Which condition should be matched in range 2?
• and so on...

Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.