Filtered Counif
in [Functions]
|
From: S on 9 Dec 2009 04:40 really good formulla for filtered countif. Can it work for sumif also? T. Valko wrote: Try 05-Aug-09 Try this: =SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q301)-ROW(Q11),,1)),--(Q11:Q301>249)) -- Biff Microsoft Excel MVP Previous Posts In This Thread: On Wednesday, August 05, 2009 9:06 AM Mr wrote: using countif in a filtered range is it possible to use countif in a range that has been filtered? my formula seems to be counting all data in the range. =COUNTIF(Q11:Q301,">249") your help would be greatly appreciated On Wednesday, August 05, 2009 9:33 AM Eduard wrote: RE: using countif in a filtered range Hi, take a lood at Debra web http://www.contextures.com/xlFunctions04.html#Filter "Mr E" wrote: On Wednesday, August 05, 2009 10:42 AM ShaneDevenshir wrote: RE: using countif in a filtered range Hi, =SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(11:301)-1,,1))*(Q11:Q301>249) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Mr E" wrote: On Wednesday, August 05, 2009 12:35 PM Mr wrote: Hi Shane,the formula worked after I added another closure to the parentheses, Hi Shane, the formula worked after I added another closure to the parentheses, but did not return the proper answer. any idea? "Shane Devenshire" wrote: On Wednesday, August 05, 2009 1:42 PM T. Valko wrote: Try Try this: =SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q301)-ROW(Q11),,1)),--(Q11:Q301>249)) -- Biff Microsoft Excel MVP On Wednesday, August 05, 2009 2:18 PM Mr wrote: Re: using countif in a filtered range I would not have a clue why that works, but it does. Thanks "T. Valko" wrote: On Wednesday, August 05, 2009 2:31 PM T. Valko wrote: You're welcome. Thanks for the feedback! You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP On Thursday, August 27, 2009 3:51 PM Jay wrote: Hey, this works great for something I'm doing as well. Hey, this works great for something I am doing as well. But I cannot understand how this works. Is there any chance you could explain what is going on with this formula? I do not get it. Thanks, Jay "T. Valko" wrote: On Thursday, August 27, 2009 10:31 PM T. Valko wrote: Re: using countif in a filtered range Let's look at this small example... Suppose this is your data: ...........A..........B 1....Region...Units 2........1..........46 3........1..........12 4........3..........16 5........4..........98 6........2..........92 7........3..........28 We want to be able to count how many units are >25 when the data is filtered. =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),,1)),--(B2:B7>25)) When the data is unfiltered the result is 4. If we filter on Region 1 then the result is 1. Here is how it works... The SUBTOTAL function allows us to perform calculations on filtered data but the SUBTOTAL function is (very) limited to the types of calculations it can perform without some trickeration. For this to work we need to generate an array of subtotals, one for each row in our range. We do that using the OFFSET function. Basically, OFFSET allows us to step through the referenced range one row at a time getting a result from each row and generating an array of results. In this example we are using SUBTOTAL to count how many cells in the range are not empty. As it steps through each cell the result will be either 1 or 0. When the data is filtered the count for those visible cells that are not empty will be1 and the count for those cells hidden by the filter or are empty will be 0. When the data is unfiltered (all cells visible) then we get an array of subtotals that are all 1s. Based on the above sample data that array would be: {1;1;1;1;1;1} This array is the first argument in the SUMPRODUCT function. SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),,1)), Let's assume we filter the data on Region 1. ...........A..........B 1....Region...Units 2........1..........46 3........1..........12 Now our array of subtotals would be: {1;1;0;0;0;0} Remember, 1 = cell is not empty *and* the cell is visible, 0 = cell is either empty or the cell is not visible. So that is how we handle the filtered data. Now we have to complete the calculation which is to count how many units are >25. We use the second argument of the SUMPRODUCT function to test every cell in the range for being >25: SUMPRODUCT(.....,--(B2:B7>25)) This will return an array of either TRUE or FALSE: B2>25=TRUE B3>25=FALSE B4>25=FALSE B5>25=TRUE B6>25=TRUE B7>25=TRUE SUMPRODUCT works with numbers so we need to convert those logicals to numbers. One way to do that is to use the double unary "--". --(B2:B7>25) --TRUE=1 --FALSE=0 Now we have another array of 1s and 0s: {1;0;0;1;1;1} SUMPRODUCT then multiplies these 2 arrays together: ......subtotals............>25....... {1;1;0;0;0;0}* {1;0;0;1;1;1} 1*1=1 1*0=0 0*0*0 On Friday, August 28, 2009 9:36 AM Jay wrote: Re: using countif in a filtered range Excellent! Thank you. "T. Valko" wrote: On Friday, August 28, 2009 11:12 AM T. Valko wrote: You're welcome! You're welcome! -- Biff Microsoft Excel MVP Submitted via EggHeadCafe - Software Developer Portal of Choice Viewing Tech-Ed 2005 Content for Non-Attendees http://www.eggheadcafe.com/tutorials/aspnet/c58c2549-108d-4ea6-bdca-a04feac064b2/viewing-teched-2005-cont.aspx
From: T. Valko on 9 Dec 2009 16:19 >Can it work for sumif also? Yes. How about providing some details of what you want to do. -- Biff Microsoft Excel MVP <S Dey> wrote in message news:200912944027sachin.dey(a)gmail.com... > really good formulla for filtered countif. Can it work for sumif also? > > > > T. Valko wrote: > > Try > 05-Aug-09 > > Try this: > > =SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q301)-ROW(Q11),,1)),--(Q11:Q301>249)) > > -- > Biff > Microsoft Excel MVP > > Previous Posts In This Thread: > > On Wednesday, August 05, 2009 9:06 AM > Mr wrote: > > using countif in a filtered range > is it possible to use countif in a range that has been filtered? > my formula seems to be counting all data in the range. > =COUNTIF(Q11:Q301,">249") > > your help would be greatly appreciated > > On Wednesday, August 05, 2009 9:33 AM > Eduard wrote: > > RE: using countif in a filtered range > Hi, > take a lood at Debra web > > http://www.contextures.com/xlFunctions04.html#Filter > > "Mr E" wrote: > > On Wednesday, August 05, 2009 10:42 AM > ShaneDevenshir wrote: > > RE: using countif in a filtered range > Hi, > > =SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(11:301)-1,,1))*(Q11:Q301>249) > > -- > If this helps, please click the Yes button. > > Cheers, > Shane Devenshire > > > "Mr E" wrote: > > On Wednesday, August 05, 2009 12:35 PM > Mr wrote: > > Hi Shane,the formula worked after I added another closure to the > parentheses, > Hi Shane, > the formula worked after I added another closure to the parentheses, but > did > not return the proper answer. any idea? > > "Shane Devenshire" wrote: > > On Wednesday, August 05, 2009 1:42 PM > T. Valko wrote: > > Try > Try this: > > =SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q301)-ROW(Q11),,1)),--(Q11:Q301>249)) > > -- > Biff > Microsoft Excel MVP > > On Wednesday, August 05, 2009 2:18 PM > Mr wrote: > > Re: using countif in a filtered range > I would not have a clue why that works, but it does. Thanks > > "T. Valko" wrote: > > On Wednesday, August 05, 2009 2:31 PM > T. Valko wrote: > > You're welcome. Thanks for the feedback! > You're welcome. Thanks for the feedback! > > -- > Biff > Microsoft Excel MVP > > On Thursday, August 27, 2009 3:51 PM > Jay wrote: > > Hey, this works great for something I'm doing as well. > Hey, this works great for something I am doing as well. But I cannot > understand how this works. Is there any chance you could explain what is > going on with this formula? I do not get it. > > Thanks, > > Jay > > "T. Valko" wrote: > > On Thursday, August 27, 2009 10:31 PM > T. Valko wrote: > > Re: using countif in a filtered range > Let's look at this small example... > > Suppose this is your data: > > ..........A..........B > 1....Region...Units > 2........1..........46 > 3........1..........12 > 4........3..........16 > 5........4..........98 > 6........2..........92 > 7........3..........28 > > We want to be able to count how many units are >25 when the data is > filtered. > > =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),,1)),--(B2:B7>25)) > > When the data is unfiltered the result is 4. If we filter on Region 1 then > the result is 1. > > Here is how it works... > > The SUBTOTAL function allows us to perform calculations on filtered data > but > the SUBTOTAL function is (very) limited to the types of calculations it > can > perform without some trickeration. > > For this to work we need to generate an array of subtotals, one for each > row > in our range. We do that using the OFFSET function. Basically, OFFSET > allows > us to step through the referenced range one row at a time getting a result > from each row and generating an array of results. > > In this example we are using SUBTOTAL to count how many cells in the range > are not empty. As it steps through each cell the result will be either 1 > or > 0. When the data is filtered the count for those visible cells that are > not > empty will be1 and the count for those cells hidden by the filter or are > empty will be 0. > > When the data is unfiltered (all cells visible) then we get an array of > subtotals that are all 1s. Based on the above sample data that array would > be: > > {1;1;1;1;1;1} > > This array is the first argument in the SUMPRODUCT function. > > SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),,1)), > > Let's assume we filter the data on Region 1. > > > ..........A..........B > 1....Region...Units > 2........1..........46 > 3........1..........12 > > Now our array of subtotals would be: > > {1;1;0;0;0;0} > > Remember, 1 = cell is not empty *and* the cell is visible, 0 = cell is > either empty or the cell is not visible. > > So that is how we handle the filtered data. Now we have to complete the > calculation which is to count how many units are >25. > > We use the second argument of the SUMPRODUCT function to test every cell > in > the range for being >25: > > SUMPRODUCT(.....,--(B2:B7>25)) > > This will return an array of either TRUE or FALSE: > > B2>25=TRUE > B3>25=FALSE > B4>25=FALSE > B5>25=TRUE > B6>25=TRUE > B7>25=TRUE > > SUMPRODUCT works with numbers so we need to convert those logicals to > numbers. One way to do that is to use the double unary "--". > > --(B2:B7>25) > > --TRUE=1 > --FALSE=0 > > Now we have another array of 1s and 0s: > > {1;0;0;1;1;1} > > SUMPRODUCT then multiplies these 2 arrays together: > > .....subtotals............>25....... > {1;1;0;0;0;0}* {1;0;0;1;1;1} > > 1*1=1 > 1*0=0 > 0*0*0 > > On Friday, August 28, 2009 9:36 AM > Jay wrote: > > Re: using countif in a filtered range > Excellent! Thank you. > > "T. Valko" wrote: > > On Friday, August 28, 2009 11:12 AM > T. Valko wrote: > > You're welcome! > You're welcome! > > -- > Biff > Microsoft Excel MVP > > > Submitted via EggHeadCafe - Software Developer Portal of Choice > Viewing Tech-Ed 2005 Content for Non-Attendees > http://www.eggheadcafe.com/tutorials/aspnet/c58c2549-108d-4ea6-bdca-a04feac064b2/viewing-teched-2005-cont.aspx
|
Pages: 1 Prev: E2561412.CAB using a 2003 Excel in 2007 Excel Next: List Disctinct Strings from Range of Cells |