SUMIF with Subtotals

[jon_wolf69]

Hi All,

I am having a problem with a formula I think I should be able to get correct but not sure if I am able to do so.

On the attached file I have a filter on the TIER's for "Is grater than or equal to 5"

What I need is a SUMIF formula that will take into account the filters. This formula needs to separate out between "GER", "IRE" and "UK" in cells C37, C38 and C39.

I have a subtotal in cell C35 which gives me the subtotal of all countries but I'd like to be able to have the subtotal separated out between the 3 countries and also still have the ability to manipulate the data so I could select different TIER's or a range of TIER's and Cells C37 - 39 automatically update themselves.

Is this possible?

Thanks for you help in advance.

Jon

[NBVC]

Try in C37:

=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$1:$C$32,ROW($C$1:$C$32)-MIN(ROW($C$1:$C$32)),,1)),--($B$1:$B$32=B37))

copied down.

[jon_wolf69]

Works perfectly!!! Thank you very much. My little Excel brain would have never worked that one out ;-)

It's Rather more complex than I would have thought though.

Regards,
Jon

[NBVC]

You are welcome.

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save