Using Subtotal on Filtered Data with an additional condition

I'm looking to add up filtered data using something like SUBTOTAL(109, range), where range is (say) $I9:$I$754. However, I only want to add those values where the contents in colum D of the row is (say) "B".

So - I'm trying to apply another condition to the SUBTOTAL in addition to the Filter.

Many thanks

Hi i am giving with example

a2 A 10
a3 A 20
a4 A 30
a5 B 100
a6 B 100
a7 C 25
a8 C 14


so now you want total only if column b contains A
then apply following formula

Please Login or Register  to view this content.

PLEASE let me know if it is working?

Originally Posted by Tanya1

Hi i am giving with example

a2 A 10
a3 A 20
a4 A 30
a5 B 100
a6 B 100
a7 C 25
a8 C 14


so now you want total only if column b contains A
then apply following formula

Please Login or Register  to view this content.

PLEASE let me know if it is working?

Thanks for response, but I believe that's incorrect as it does not take account of the Filter applied to the range. See my original post.

please share your file

Originally Posted by Tanya1

please share your file

Your sample data above could be used. Imagine we have applied a filter so that only rows with the Col C value >= 30 are showing. So only rows 4-6 are visible (others are hidden by the filter):

a4 A 30
a5 B 100
a6 B 100

Now, I want to add up the Col C values where (say) Col B = "A". The sum value I want to get therefore is 30.

Originally Posted by Fotis1991

Hi

You can use.

=SUMPRODUCT(--(D2:D754="B"),SUBTOTAL(9,OFFSET(I2:I754,ROW(I2:I754)-ROW(I2),0,1)))

Thanks for this. Can you explain why you need the OFFSET function, rather than just explicitly inserting the range here (eg. I2:I754)?

c if such is the case that is
1. If i column a in which i want to take only those value which are >10
2.and only if column b contains a
i will not use filter for sure
e.g

30 A
20 A
25 A
30 A
10 B
10 A
40 C

Please Login or Register  to view this content.

or is it necessary for you to apply filter because with sumproduct u can apply multiple conditions and calculate sum accordingly.

Originally Posted by JosephP

the offset function returns an array of discrete ranges each of which is evaluated by subtotal so that only the visible cells are included in the total-for hidden rows the subtotal returns 0

Joseph - thanks for the explanation. However, if we use 109 instead of 9 as the first Argument in the SUBTOTAL, doesn't that do the same thing (ie. limit scope to the visible rows). [By the way, I'm using Excel 2010 if that's relevant.]

Originally Posted by Fotis1991

As my English are not good, i'd like to thanks Joseph for all the explanations.

Thanks for your solution. It does work! I will have to do some more work to fully understand it though!

Originally Posted by JosephP

no-whether you use 9 or 109 makes no difference here. without the offset function subtotal would return one total for the entire range. with the offset it returns an array of values (0 for filtered rows and the value for visible rows)

Joseph - thanks for the explanation. I need to do a bit more study (after a good night's sleep!)