woensdag 17 september 2014

Excel: Pivot Tables Versus Functions Like SUMIFS, AVERAGEIFS And COUNTIFS; Ad Hoc Versus Standard

Thee Excel pivot table may be the best tool in the market for creating ad hoc report. We can very rapidly compare data from various angles.

As soon as we are dealing with standard reports, reports with a longer time span and standardized results, I personally think the pivot table is less suitable as a reporting instrument.

In that case I prefer Excel functions like:

  • SUMIFS
  • AVERAGESIFS
  • COUNTIFS


To build reports like this is definitely more complex and time consuming. In return you get longer lasting reports.

In the following example I created three examples to compare SUMIFSAVERAGEIFS and COUNTIFS to the similar pivot tables.


The examples are based on a dataset. Examples and dataset you can download. In the dowload it is easy to see how I used the different functions.

Even in these relatively simple examples you can see an important diffenence. If you select a specific country the left examples still show all rows and columns. Some rows and columns disappear from the pivot table.

You can download the file ExcelPivotTableDifferent.xlsx via:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Een reactie plaatsen