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:
|| Amount USD
|2020-01-28|| Sylvester Stallone
|2020-02-17|| Arnold Schwarzenegger
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:
This results in the following formula:
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:
At the end this gives us the following formula:
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.
This results in the following generic formula:
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 Container Containers CouchDB DB DNS Database Docker ELK Elasticsearch Filebeat FreeBSD Galera GlusterFS Grafana Graphics HAProxy HTML Hacks Hardware Icinga Icingaweb2 InfluxDB 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 Redis SSL Samba Seafile Security Shell SmartOS Solaris Surveillance SystemD Systemd TLS Tomcat Ubuntu Unix VMWare VMware Varnish Virtualization Windows Wireless Wordpress Wyse ZFS Zoneminder