Hi.

I'm late to the party. If I understand correctly the filtering takes care of the SUMIFS part.

This works at my end ... piggy backing on Glenn's upload ... a simple
Formula: copy to clipboard 
=SUBTOTAL(9,A2:D16)
does what I understand you to want. Apply filters as desired.

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
SUBTOTAL doesn't generate a range. It generates an array in this case (revealed by Evaluate formula). The same size rule still applies.

OFFSET can "sub-divide" a range into smaller sub ranges. SUBTOTAL accepts this since ranges are the only thing SUBTOTAL will process.