SUMIF only filtered data

Question

I have an excel formula that sums a particular column of data.

SUMIF(Data!$E:$E,$E$89,Data!$F:$F)

I'm looking to add a column G which I want to filter over. So, ideally, when I click Data->Filter, I can make SUMIF only sum whatever I filter in column G.

Is there a good way of doing so?

The data looks something like this:

E     F         G
111   20050719  Foreign
112   20050719  Domestic
576   20050719  Foreign
624   19910101  Domestic

$E$89 = 20050719

Answer 1

The formula you want is taken and modified from this post; CountIf With Filtered Data

=SUMPRODUCT(SUBTOTAL(9,OFFSET(E2:E7,ROW($F$2:$F$7)-MIN(ROW($F$2:$F$7)),,1)),(E89=$F$2:$F$7)+0)

Answer 2

I don't believe SUMIF etc have this functionality, however the SUBTOTAL function can do this. (Another option using SUMIFS which allows for multiple conditions)

=SUBTOTAL(9,Data_Range_To_Sum)

https://support.office.com/en-gb/article/SUBTOTAL-function-7b027003-f060-4ade-9040-e478765b9939?ui=en-US&rs=en-GB&ad=GB&fromAR=1

Answer 3

I am handling the same problem right know and I seem to have found a solution.

The screenshot shall be selfexplanatory - however the key is in placing the SUMIFS "subtotals" condition in the same column as the conditions in the data itself. The SUBTOTAL counts from the SUMIFS "subtotals" and all is filtered by the condidions.