Sumif for Visible range when using filter
 

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

Sumif for Visible range when using filter
 

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)

"anshu" wrote:

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

Sumif for Visible range when using filter
 

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"
--
Regards

Roger Govier


"anshu" wrote in message
ps.com...
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

Sumif for Visible range when using filter
 

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" wrote:

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

Sumif for Visible range when using filter
 

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

On Jul 16, 7:58 pm, Mike H wrote:
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" wrote:
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