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

Written by - 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.

This results in the following formula:

=SUMIF(B2:B4,"Sylvester Stallone",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:

LibreOffice Calc SUMIF example

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...

At the end this gives us the following formula:

=SUMIFS(Details.E2:E14,Details.B2:B14,"Sylvester Stallone",Details.D2:D14,"Food")

Formula explained: Calc will create a total sum of fields E2 until E14 in tab "Details" if the corresponding field in column B matches "Sylvester Stallone" and the corresponding field in column D matches "Food".

By using this formula and adjusted for each cast and expense type, a nice overview can be created:

Great, now we can see which actor used how much expenses on each type of expense.

Using variables in SUMIFS

One disadvantage of the above solution is that each formula needs to be manually adjusted for the different criteria. But by using the actual expense types (column A) and the cast names (row 1), the formula can be adjusted to match these two.

  • Instead of using a hard-coded name ("Sylvester Stallone") in the first criteria, the criteria should now match the value from B$1 ($1 tells Calc to not change the row number when the formula is applied on other cells). 
  • Instead of using a hard-coded category ("Food") in the second criteria, the criteria shoujld now match the value from $A2 ($A tells Calc to not change the column when the formula is applied on other cells).
  • The ranges (sum and criteria ranges) need to be fixed using dollar signs before both the column and row

This results in the following generic formula:

=SUMIFS(Details.$E$2:$E$14,Details.$B$2:$B$14,B$1,Details.$D$2:$D$14,$A2)

The cell can now be "pulled" horizontally and vertically and it spreads around the table. Each formula automatically adjusts itself to the "headers", meaning the relevant name of the person in row one and the expense type in column A.

LibreOffice Calc SUMIFS example with dynamic formula


Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.