wimgielis.com

Summing over multiple criteria

• Sep. 4, 2015

	Example files with this article:
	Summing over multiple criteria

Introduction

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
• =DSUM()
• =SUMPRODUCT()
• =SUM() as a matrix formula, …
• =SUMIFS() (vanaf Excel 2007)
• =MMULT()
• using a pivot table, combined with slicers or timelines
• using a pivot table, combined with SQL expressions (web link)

Even relatively easy lists and tasks in Excel with what seem to be straightforward operations, can turn out to be quite difficult in Excel, provided that you want to avoid manual work. There is a tension between "a nice handy layout" and "simple Excel formulas": you indeed make it more difficult to write Excel formulas when the input is not structured and foremost trying to present well. I will illustrate this below. Very often we just need simple sums in Excel, like beer consumption from November 30 until December 6:

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 !

• « Previous Excel article
• Next Excel article »