问题:

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

回答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)

回答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