Скачать или смотреть SUMPRODUCT visible cells only

00:00 SUMPRODUCT that adjusts if we filter/ hide rows
00:11 Use AGGREGATE function in Excel
00:39 Test what AGGREGATE sees when the row is hidden
00:49 Create the SUMPRODUCT
01:12 Filter/ hide the rows and see SUMPRODUCT change

From https://www.auditexcel.co.za/blog/sum...

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.

In the example below we want to be able to determine the weighted average selling price per department when we filter on the department

SUMPRODUCT only visible cells

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.

SUMPRODUCT only visible cells

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).

SUMPRODUCT only visible cells

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

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.

SUMPRODUCT only visible cells