This shows a simple way to SUMPRODUCT only visible cells. Typically this happens when you are trying to do a weighted average. SUMPRODUCT is great for weighted average calculations, but sometimes you want to be able to filter/ hide cells and see what the resultant weighted average is for the cells that are visible.
Table of contents
YouTube SUMPRODUCT only visible cells
Watch, like, subscribe
In the example below we want to be able to determine the weighted average selling price per department when we filter on the department
Use AGGREGATE to differentiate the hidden cells
First thing we need to do is to use the (relatively) new AGGREGATE function. It is similar to SUBTOTAL in that you can change a setting so that it calculates on all cells or only the visible cells.
So below, in Column A we have created an AGGREGATE but we are only looking at one cell at a time (which goes against the concept of AGGREGATE but you will see why). If you are unfamiliar with AGGREGATE, the Function Number 2 tells it to do a COUNT and Option 5 tells it to ignore hidden cells. In A4 we are telling it to only count the number of cells in D4 (which is clearly only ever going to be a 1).
Just note that cell A5 shows a 1 below.
The reason this works is shown when you now hide some rows. See below we have hidden department B, but before we did this, in cell A1 I created a formula looking at cell A5 (remember above when we looked at A5 it showed a 1). When it is hidden, cell A5 turns to a 0 (this is how AGGREGATE works).
Use SUMPRODUCT with this extra Visible Cells Array
Once this is set up, we can just create the normal SUMPRODUCT except with one extra array to differentiate between visible and hidden.
As shown below, in cell E13 we created a SUMPRODUCT.
To get a weighted average you generally need to divide by one of the arrays, in this case the Units Sold. So in order to get the correct weighted average, the visible cells trick must be used on the numerator and denominator.
SUMPRODUCT only visible cells
So now when we hide or filter rows (you can do either) your SUMPRODUCT will ignore the hidden cells (because it is multiplying by 0). As shown below, when we are looking at Department A only, cell E13 (the selections weighted average) differs from E11 which is a normal SUMPRODUCT which takes into account all cells whether visible or hidden.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
Excel spreadsheet open but not visible
Summing, Counting, or Averaging visible cells only in Excel