sumifs and filtered data

Hi everyone,

I have an issues here with using a sumifs function. Working great apart from when I need to filter the data in the table its referencing. Does anyone know how to get this to work properly? I've attached a spreadsheet to show what I'm referring to.

"Sheet1" has the sumifs and the data I'm looking to pull. "ALSchedule" contains the data.

So for example. Currently I have a filter applied to "Type" column in "ALSchedule". In "Sheet1", cell b5, the amount that should have been showing at the moment should have been $102,965,001.92. But, the sumifs is picking up the filtered-out categories which you'll be able to see.

If anyone can help me with a workaround that'd be great. Thank you!

Try like this,

B2,

=SUMPRODUCT(SUBTOTAL(9,OFFSET(ALSchedule!A$2,ROW(ALSchedule!$A$2:$A$2000)-ROW(ALSchedule!$A$2),,1)),--(ALSchedule!$D$2:$D$2000=$A5),--(ALSchedule!$G$2:$G$2000>=2))

copy to C2.

D2,

=SUMPRODUCT(SUBTOTAL(9,OFFSET(ALSchedule!A$2,ROW(ALSchedule!$A$2:$A$2000)-ROW(ALSchedule!$A$2),,1)),--(ALSchedule!$C$2:$C$2000=$A5),--(ALSchedule!$G$2:$G$2000>=2))

Copy to E2, then all copy down.

Do not use whole reference like A:A, use it like A1:A20000 or use a dynamic range.

Try:

Please Login or Register  to view this content.

copied down and across to next column...

then in D2, adjust the D2:D2500 range to C2:C2500 and copy down and to next column