Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No. of rows selected from auto filter not visible on toolbar? | Excel Discussion (Misc queries) | |||
how do i select all the visible cells only when using auto filter | Excel Discussion (Misc queries) | |||
Return the first visible cell below filter labels | Excel Worksheet Functions | |||
SumIf in Visible Cell Range | Excel Worksheet Functions | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |