Home 
Search 
Today's Posts 
#31




How can I count items in a filtered list?
Hi.
I was wondering if you could explain the occurence of TWO adjacent minus signs part way through this formula. Thanks, David "T. Valko" wrote: This will "COUNTIF" B2:B100 = "A" in a filtered list: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)ROW(B2),0,1)),(B2:B100="A"))  Biff Microsoft Excel MVP "tommy" wrote in message news do anybody know, how to use "countif" with filtered lists? the function subtotal does not allow conditions (e.g. "name") the function countif does it, but it count hidden cells, too thank you for your ideas "N Harkawat" wrote: =subtotal(2,a1:a1000) "Counting filtered data." <Counting filtered wrote in message ... I have an excel spread sheet table that I filter. I would like to be able to count the number of cells in the filtered data. Anyone know how to do this? CountA returns the number of cells in the unfiltered data. Tom 
#32




How can I count items in a filtered list?
(B2:B100="A")
This expression will return an array of either TRUE or FALSE: (B2:B100="A") B2: X B3: A B4: A B5: C B2="A" = FALSE B3="A" = TRUE B4="A" = TRUE B5="A" = FALSE SUMPRODUCT calculates numbers so we have to convert those logical TRUE and FALSE to numbers. The TWO adjacent minus signs, known as double unary, is one way to do that. TRUE = 1 FALSE = 0 (B2="A") = 0 (B3="A") = 1 (B4="A") = 1 (B5="A") = 0 So we end up with an array of 1s and 0s: {0;1;1;0} The result of the SUBTOTAL function is also an array of 1s and 0s. For example: {0;1;1;1}. These 2 arrays are then multiplied together to arrive at the final result of the formula: Subtotal......B2:B5="A" {0;1;1;1}*{0;1;1;0} 0*0 = 0 1*1 = 1 1*1 = 1 1*0 = 0 SUMPRODUCT({0;1;1;0}) = 2 See this for more info: http://xldynamic.com/source/xld.SUMPRODUCT.html  Biff Microsoft Excel MVP "David" wrote in message ... Hi. I was wondering if you could explain the occurence of TWO adjacent minus signs part way through this formula. Thanks, David "T. Valko" wrote: This will "COUNTIF" B2:B100 = "A" in a filtered list: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)ROW(B2),0,1)),(B2:B100="A"))  Biff Microsoft Excel MVP "tommy" wrote in message news do anybody know, how to use "countif" with filtered lists? the function subtotal does not allow conditions (e.g. "name") the function countif does it, but it count hidden cells, too thank you for your ideas "N Harkawat" wrote: =subtotal(2,a1:a1000) "Counting filtered data." <Counting filtered wrote in message ... I have an excel spread sheet table that I filter. I would like to be able to count the number of cells in the filtered data. Anyone know how to do this? CountA returns the number of cells in the unfiltered data. Tom 
#33




How can I count items in a filtered list?
This works (great); however, it seem to work only for nonnumerical data. I
am analyzing a survey the numerical responses where 5 = strongly agree, 4 =agree, etc. I really don't want to convert it all to alphacharacters since I am performing other statistical functions on those cells. Any ideas? "T. Valko" wrote: This will "COUNTIF" B2:B100 = "A" in a filtered list: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)ROW(B2),0,1)),(B2:B100="A"))  Biff Microsoft Excel MVP "tommy" wrote in message news do anybody know, how to use "countif" with filtered lists? the function subtotal does not allow conditions (e.g. "name") the function countif does it, but it count hidden cells, too thank you for your ideas "N Harkawat" wrote: =subtotal(2,a1:a1000) "Counting filtered data." <Counting filtered wrote in message ... I have an excel spread sheet table that I filter. I would like to be able to count the number of cells in the filtered data. Anyone know how to do this? CountA returns the number of cells in the unfiltered data. Tom 
#35




How can I count items in a filtered list?
Hi just a noob here,
I would like to know how or what function do I need to use on this kind of problem. I am creating a template where the 2nd column B3:B50 has a drop down option, so I'd like that Column to be counted examples on the drop down are . NET  TNBA Callbacks and NRA  Montreal EMT. Ex. if B2:B50 have (10) . NET  TNBA Callbacks it will appear (10) on J15 and if NRA  Montreal EMT have (3) it must appear (3) on J16. hope that helps in explaining...its too hard to explain, I am wishing if I could attach the file for better view.. thanks again.. waiting for your response 
#36




How can I count items in a filtered list?
the 2nd column B3:B50 has a drop down option
Assuming that means you have AutoFilter applied... Try these: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B50,ROW(B3:B50)ROW(B3),0,1)),(B3:B50="NET  TNBA Callbacks")) =SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B50,ROW(B3:B50)ROW(B3),0,1)),(B3:B50="NRA  Montreal EMT"))  Biff Microsoft Excel MVP "kosageinusha18" wrote in message ... Hi just a noob here, I would like to know how or what function do I need to use on this kind of problem. I am creating a template where the 2nd column B3:B50 has a drop down option, so I'd like that Column to be counted examples on the drop down are . NET  TNBA Callbacks and NRA  Montreal EMT. Ex. if B2:B50 have (10) . NET  TNBA Callbacks it will appear (10) on J15 and if NRA  Montreal EMT have (3) it must appear (3) on J16. hope that helps in explaining...its too hard to explain, I am wishing if I could attach the file for better view.. thanks again.. waiting for your response 
#37




How can I count items in a filtered list?
I'm trying to do something similar but when I use this formula i keep getting this, "Error: Argument Must be a range"
On Wednesday, May 13, 2009 at 11:00:41 PM UTC7, T. Valko wrote: This will "COUNTIF" B2:B100 = "A" in a filtered list: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)ROW(B2),0,1)),(B2:B100="A"))  Biff Microsoft Excel MVP "tommy" wrote in message news do anybody know, how to use "countif" with filtered lists? the function subtotal does not allow conditions (e.g. "name") the function countif does it, but it count hidden cells, too thank you for your ideas "N Harkawat" wrote: =subtotal(2,a1:a1000) "Counting filtered data." <Counting filtered wrote in message ... I have an excel spread sheet table that I filter. I would like to be able to count the number of cells in the filtered data. Anyone know how to do this? CountA returns the number of cells in the unfiltered data. Tom 
#38




How can I count items in a filtered list?
On Thursday, July 21, 2005 at 1:09:14 PM UTC4, filtered Counting filtered data. wrote:
I have an excel spread sheet table that I filter. I would like to be able to count the number of cells in the filtered data. Anyone know how to do this? This does not Work I need to count the number for each month (filtered) Please help =MONTH(SUBTOTAL(3,AL12:AL10000)=5) 
#39




How can I count items in a filtered list?
On Friday, July 22, 2005 at 3:09:14 AM UTC+10, filtered Counting filtered data. wrote:
I have an excel spread sheet table that I filter. I would like to be able to count the number of cells in the filtered data. Anyone know how to do this? CountA returns the number of cells in the unfiltered data. Tom Use the Subtotal function Function 103 counts only the visible rows in a range Example : =subtotal(103,C2:C198) Helen 
#40




How can I count items in a filtered list?
On Thursday, July 21, 2005 at 7:10:09 PM UTC+2, N Harkawat wrote:
=subtotal(2,a1:a1000) "Counting filtered data." <Counting filtered wrote in message ... I have an excel spread sheet table that I filter. I would like to be able to count the number of cells in the filtered data. Anyone know how to do this? CountA returns the number of cells in the unfiltered data. Tom This worked for me, thanks! Simple easy solution. You can check in the help file for this function, you add "10" before the function code, in this case "2", thus input "102" to show only "visible" cells. Thus filtered or hidden cells are excluded from the count function. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
named range, data validation: list nonselected items, and new added items  Excel Discussion (Misc queries)  
Count Position of Filtered TEXT cells in a column  Excel Worksheet Functions  
Count Position of Filtered TEXT cells in a column  Excel Worksheet Functions  
Items in a List  Excel Discussion (Misc queries)  
How do I find out what items are in one list but not in another l.  Excel Discussion (Misc queries) 