Hi everyone,
I want to be able to use a SUMIFS formula on a filtered list. So summing based on multiple criteria but only on the visible cells. I have seen a number of solutions for this which seem to use a combination of SUMPRODUCT, SUBTOTAL and OFFSET but I am struggling to understand the syntax and why these work. Was hoping someone could help me with this.
For example one solution I have seen uses this formula:
=SUMPRODUCT(SUBTOTAL(9,OFFSET(E$10:E$33,ROW(E$10:E$33)-ROW(E$10),0,1,1)),--($D$10:$D$33=$D5))
I thought SUMPRODUCT used two or more same sized ranges. How does SUBTOTAL generate a range? And what does the OFFSET formula do in this formula? Also how does the last part of the SUMPRODUCT formula work - is this like an IF statement and what does the "--" do - I've never seen that in a formula before?
Sorry this is a bit of an odd question but if I can understand the syntax then I can hopefully adapt for my situation.
Thanks in advance.
Bookmarks