Sumif for Visible range when using filter

[anshu]

I have three columns of data...

My Data is in Column C and Descriptions are in Column A and B.

I have a sumif function at the end of the sheet which adds Column C
based on if Column B has a given value. But whne I filter the data
using Column A, I need the sumif to give the sum only for the visible
range and not for the entire range

For example

desc type Data
r a 1
r a 1
r a 1
r a 1
s a 1
r b 2
r b 2
r b 2

=SUMIF(B2:B8,"a", C2:C8)

On filtering S in Column A I am getting only one row of data with
value of 1 and type A. That's what I want as answer. But it is giving
the answer as 5 (for the entire range)..

Which is the most effective way to do this..

Please help

Thanks in advance

Anshuman

 

[Guest]

Look at SUMPRODUCT:

=SUMPRODUCT(--(a2:a8="s"),--(b2:b8="a"),C2:C8)

Better to use cells to hold the values being compared:

X1="s"
X2="a"

=SUMPRODUCT(--(a2:a8=X1),--(b2:b8=X2),C2:C8)

 

[Roger Govier]

Hi

Without having to filter
=SUMPRODUCT((A2:A8="s")*(B2:B8="a")*(C2:C8))

With filtering
=SUBTOTAL(9,C2:C8)
having applied a filter to column B as well for "a"

 

[Guest]

Anshu,

Apply your filter to column A and then a second filter to column B so that
only 'a' is showing then total column C with this formula:-

=SUBTOTAL(9, C2:OFFSET(C19,-1,0))

Mike

 

[anshu]

Hi Toppers, Roger and Mark.

Thanks for the answers and apologizing for thanking so late. I used
the solution couple of days back and then forgot to reply and thank
you all. All the solution helped me

Thanks again,
Anshuman