Excel 2010 pivot table data filtering

Question

I have a pivot table with each row having an error code (say, widget defect code, with 0 being no error). I want to be able to summarize the data with total error rates, but I'd also like to summarize by excluding specific error types. So the basic forumula is obviously (rows with error code > 0) / (total rows). However, when you filter a pivot table, you remove some rows from the numerator, but they also get removed from the denominator.

Of course, if the number of rows with errors is much smaller than the total number of rows, this isn't such a big deal, but there are some other stats in the table that I'd like to preserve (ie, I don't want the rows removed for the purpose of those calculations)

Is there a way to filter so that the excluded rows are excluded only for the error calculation, but retained for the other summary values (widget count, widget weight)?

Hope that makes sense!

Answer 1

The SUBTOTAL function is purpose built for this. It can do Average, Count and Sum (and many other things too) and can optionaly include or exclude hidden rows