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

Solution 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)

Solution 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

Comments

[Lewis Fogden]

Subtotal does not include any filtered data.