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.

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:

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.

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.

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.

No comments yet.

AWS Android Ansible Apache Apple Atlassian BSD Backup Bash Bluecoat CMS Chef Cloud Coding Consul Containers CouchDB DB DNS Database Databases Docker ELK Elasticsearch Filebeat FreeBSD Galera GlusterFS Grafana Graphics HAProxy HTML Hacks Hardware Icinga Icingaweb Icingaweb2 Influx Internet Java KVM Kibana Kodi Kubernetes LXC Linux Logstash Mac Macintosh Mail MariaDB Minio MongoDB Monitoring Multimedia MySQL NFS Nagios Network Nginx OSSEC OTRS Office PGSQL PHP Perl Personal PostgreSQL Postgres PowerDNS Proxmox Proxy Python Rancher Rant Redis Roundcube SSL Samba Seafile Security Shell SmartOS Solaris Surveillance Systemd TLS Tomcat Ubuntu Unix VMWare VMware Varnish Virtualization Windows Wireless Wordpress Wyse ZFS Zoneminder