SUMPRODUCT(D8:D19,E8:E19)
a simple SUMPRODUCT like the above would simply give you a result of D8*E8 + D9*E9 + ….. And so on
So that gives you 65,600.
hence, we need to create another set of 1s & 0s to make sure filtered rows are not calculated. SUBTOTAL works great with filters. a simple
SUBTOTAL with function number 3 works like this:
SUBTOTAL(3,B8:B19)
it will count how many non-blank cells exist in B8:B19. what i want is a series of numbers that will give me 1 count or 0 count,
depending on whether the particular cell is filtered. that means i actually want
SUBTOTAL(3,B8)
SUBTOTAL(3,B9)
...all the way to SUBTOTAL(3,B19), but inside a single cell.
to implement such a formula, i need the range to be a dynamic one; using the OFFSET formula. there are 5 arguments you can input
inside the OFFSET, but we're just going to use 2 of them.
=OFFSET(reference,rows,cols,[height],[width])
reference
this is the anchor point. so i placed it in B8
rows
this is how many rows you want it away from the anchor point.
to test it out, try inputting:
=OFFSET(B8,0,0)
it is equivalent to putting
=OFFSET(B8,,)
this means anchor in B8, & moving 0 rows & 0 columns away from B8. so this is exactly like putting:
=B8
since we want it to be looking at B8, then B9, then B10, & so on... the "rows" is where we'll put numbers of 0 to 11. the ROW formula
comes in now. the ROW formula gives you the row number, regardless of what column it's in,
=ROW(B8)
so the above will return 8.
to put in a range, it will become an array. to see the results select the formula & press F9
=ROW(B8:B19)
you should see:
{8;9;10;11;12;13;14;15;16;17;18;19}
since we want to start from 0 to 11, you simply have to minus whatever row number you start in.
=ROW(B8:B19)-ROW(B8)
{0;1;2;3;4;5;6;7;8;9;10;11}
SUBTOTAL(3,OFFSET(B8,ROW(B8:B19)-ROW(B8),))
so if you select the SUBTOTAL & OFFSET formula above & press F9, you should be seeing:
{1;1;1;1;1;1;1;1;1;1;1;1}
this is when nothing is filtered. after filtering, you should see a couple of 0s appearing.
what SUMPRODUCT does is to multiply & add these numbers, so this is how it does it:
so you notice that whatever is filtered wont be calculated because anything multiplies by 0 is 0.
