Summing over multiple criteria
- Sep. 4, 2015
|Example files with this article:|
I do know that on this subject, a lot has been discussed and talked about. In Excel we have many different ways to make sums of data where you need to apply multiple criteria:
- =SUM() and (re)make selections every time
- =SUM() as a matrix formula, …
- =SUMIFS() (vanaf Excel 2007)
- using a pivot table, combined with slicers or timelines
- using a pivot table, combined with SQL expressions (web link)
In the table we use columns to track beer consumption day by day: the table will extend to the right. If we want to know beer consumption from November 30 until December 6, then we sum the yellow cells, or the bold daily totals, columns E to J. If you don't mind changing the cell references yourself in a manual way, then stop reading this article: that is the simplest solution. For all other readers of this article the formulas I wrote will show you how to sum over multiple criteria. Furthermore, what if we are only interested in beer consumption of just 1 brand, but the data can occur at multiple lines (see lines 3 and 5) ?
Depending on the input and how well you structure the input, you will have little or many troubles to get to working formulas: a flat data table with column A: Beer type, column B: Date, column C: Consumption works best if you ask me. De rows at the bottom of the data table will extend but the table will always contain just those 3 columns (unless requirements change of course).
Since Excel 2013 we also have timelines that you can use with a pivot table. Indeed, you are reading this fine: without a single formula you can reach the same end result as what I showed you above. That is one of the major advantages of pivot tables, you don't need to create the formulas yourself. If you base the pivot table on a data table then the pivot table will automatically extend upon refresh. Since Excel 2010 we already have the slicers for pivot tables, they also allow you to filter pivot tables.
How do we make sums in a quick way
Sinds Excel 2010 hebben we ook timelines / tijdslijnen die je kan inzetten bij een pivot table. Inderdaad, je leest het goed: zonder enige formule kan je ook hetzelfde resultaat bewerkstelligen. Dat is een van de grote voordelen van pivot tables, je moet er geen enkele formule voor maken. Baseer je de pivot table op een tabel dan breidt de pivot table zich automatisch uit wanneer je hem ververst.
All the different formula variants are shown with examples in the file at the top of the page, including a pivot table with a slicer. Honestly, this file is a must-have if you want to sum over multiple criteria !