CODEWITHSUNDEEP
excelexcel-formulaexcel-2007
user2864154
user2864154

Reputation: 475

SUMIF only filtered data

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

Upvotes: 3

Views: 33716

Answers (3)

Martin Dvořák
Martin Dvořák

Reputation: 13

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

The screenshot shall be self explanatory - 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.

enter image description here enter image description here

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

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)

enter image description here

Upvotes: 3

Lewis Fogden
Lewis Fogden

Reputation: 524

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

Upvotes: 2

Related Questions