Home 
Search 
Today's Posts 
#21




How can I count items in a filtered list?
Yep I've got 2 worksheets linked to each other. How can I make a formula
works if the cells I need to calculate are linked to the first worksheet. Hope do you get my point? "Gord Dibben" wrote: Your syntax is not correct. You cannot have Total.xls and Monthly.xls in one workbook. An *.xls is a single workbook. Do you mean you have two worksheets named Total and Monthly in a workbook named PM.xls Is this latest problem you are posting in any way related to the original posting about counting filtered items you and Biff have been working on? Gord Dibben MS Excel MVP On Thu, 6 Aug 2009 10:10:15 0700, Antonella wrote: Hi I need your help.. I know that is not the right forum but I'll try anyway. Hope you will understand what Iâ€™m trying to do. Iâ€™ve got 2 worksheets called Total.xls Monthly.xls in a workbook called PM. Iâ€™ve linked few cells from Monthly to Total (thatâ€™s was easy). Now all the formula already in Total give Error as results. Any help greatly appreciated. Antonella "T. Valko" wrote: You're welcome. Thanks for the feedback!  Biff Microsoft Excel MVP "Antonella" wrote in message ... Thanks.. You made my day!!! Antonella "T. Valko" wrote: Is a data validation drop down list. Try this: =SUMPRODUCT((A1:A100="Netherlands"),(B1:B10="July")) Better to use cells to hold the criteria. D1 = Netherlands E1 = July =SUMPRODUCT((A1:A100=D1),(B1:B100=E1))  Biff Microsoft Excel MVP "Antonella" wrote in message ... Is a data validation drop down list. Can be the reason why does not work? "T. Valko" wrote: In both columns data must be chosen from a drop down menu Is that a data validation drop down list (or maybe a combo box) or is it an Auto Filter drop down?  Biff Microsoft Excel MVP "Antonella" wrote in message ... Hi, I'm hoping that someone can help me.. I am trying to count how many export has been done for each Region for July, how many for August and so on. I'll try to make myself a bit clear. For example: My first column (A1:A100) contains 10 different Regions (Netherlands, Italy, Spain etc). The second column (B1:B100) contains months ( July, August, September, October etc). I would like to know if there is formula to count how many times Netherlands July appears in those 2 columns. In both columns data must be chosen from a drop down menu.. Hope I made myself clear.. Can this be done ? Thanks Antonella "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 
#22




How can I count items in a filtered list?
Again I ask............are your calculations concerning what you and Biff
were discussing? I have not follwed the thread so not sure what you're asking for. But you can calculate using linked cells. Say sheet1 has these linked cells in A1:A3 =Sheet2!G1 =Sheet2!H2 =Sheet2!D1 Enter =SUM(A1:A3) in any cell on sheet1 If not clear to you, post some examples of linked cells and what type of calculating you need done. Gord On Thu, 6 Aug 2009 16:46:01 0700, Antonella wrote: Yep I've got 2 worksheets linked to each other. How can I make a formula works if the cells I need to calculate are linked to the first worksheet. Hope do you get my point? "Gord Dibben" wrote: Your syntax is not correct. You cannot have Total.xls and Monthly.xls in one workbook. An *.xls is a single workbook. Do you mean you have two worksheets named Total and Monthly in a workbook named PM.xls Is this latest problem you are posting in any way related to the original posting about counting filtered items you and Biff have been working on? Gord Dibben MS Excel MVP On Thu, 6 Aug 2009 10:10:15 0700, Antonella wrote: Hi I need your help.. I know that is not the right forum but I'll try anyway. Hope you will understand what I’m trying to do. I’ve got 2 worksheets called Total.xls Monthly.xls in a workbook called PM. I’ve linked few cells from Monthly to Total (that’s was easy). Now all the formula already in Total give Error as results. Any help greatly appreciated. Antonella "T. Valko" wrote: You're welcome. Thanks for the feedback!  Biff Microsoft Excel MVP "Antonella" wrote in message ... Thanks.. You made my day!!! Antonella "T. Valko" wrote: Is a data validation drop down list. Try this: =SUMPRODUCT((A1:A100="Netherlands"),(B1:B10="July")) Better to use cells to hold the criteria. D1 = Netherlands E1 = July =SUMPRODUCT((A1:A100=D1),(B1:B100=E1))  Biff Microsoft Excel MVP "Antonella" wrote in message ... Is a data validation drop down list. Can be the reason why does not work? "T. Valko" wrote: In both columns data must be chosen from a drop down menu Is that a data validation drop down list (or maybe a combo box) or is it an Auto Filter drop down?  Biff Microsoft Excel MVP "Antonella" wrote in message ... Hi, I'm hoping that someone can help me.. I am trying to count how many export has been done for each Region for July, how many for August and so on. I'll try to make myself a bit clear. For example: My first column (A1:A100) contains 10 different Regions (Netherlands, Italy, Spain etc). The second column (B1:B100) contains months ( July, August, September, October etc). I would like to know if there is formula to count how many times Netherlands July appears in those 2 columns. In both columns data must be chosen from a drop down menu.. Hope I made myself clear.. Can this be done ? Thanks Antonella "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 
#23




How can I count items in a filtered list?
Hi,
I wonder if someone can help me, I am trying to count the number of individuals in two seperate columns on the same spreadsheet based on values that are text and numbers, i.e. "in column B1:B391 which are 'Con'" and "in column K1:K391 which have a value of '1'". i have tried the formula suggested below: =SUMPRODUCT((A1:A100="Netherlands"),(B1:B10="July")) adapting this to: =SUMPRODUCT((B1:B391="Con"),(K1:K391="1")) however this does not work. Any help would be amazing Many Thanks Dominic "T. Valko" wrote: Is a data validation drop down list. Try this: =SUMPRODUCT((A1:A100="Netherlands"),(B1:B10="July")) Better to use cells to hold the criteria. D1 = Netherlands E1 = July =SUMPRODUCT((A1:A100=D1),(B1:B100=E1))  Biff Microsoft Excel MVP "Antonella" wrote in message ... Is a data validation drop down list. Can be the reason why does not work? "T. Valko" wrote: In both columns data must be chosen from a drop down menu Is that a data validation drop down list (or maybe a combo box) or is it an Auto Filter drop down?  Biff Microsoft Excel MVP "Antonella" wrote in message ... Hi, I'm hoping that someone can help me.. I am trying to count how many export has been done for each Region for July, how many for August and so on. I'll try to make myself a bit clear. For example: My first column (A1:A100) contains 10 different Regions (Netherlands, Italy, Spain etc). The second column (B1:B100) contains months ( July, August, September, October etc). I would like to know if there is formula to count how many times Netherlands July appears in those 2 columns. In both columns data must be chosen from a drop down menu.. Hope I made myself clear.. Can this be done ? Thanks Antonella "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 
#24




How can I count items in a filtered list?
Try removing the quotes from the second criteria...
=SUMPRODUCT((B1:B391="Con"),(K1:K391=1))  Domenic Microsoft Excel MVP www.xlcentral.com Your Quick Reference to Excel Solutions In article , Dominic_gates wrote: Hi, I wonder if someone can help me, I am trying to count the number of individuals in two seperate columns on the same spreadsheet based on values that are text and numbers, i.e. "in column B1:B391 which are 'Con'" and "in column K1:K391 which have a value of '1'". i have tried the formula suggested below: =SUMPRODUCT((A1:A100="Netherlands"),(B1:B10="July")) adapting this to: =SUMPRODUCT((B1:B391="Con"),(K1:K391="1")) however this does not work. Any help would be amazing Many Thanks Dominic "T. Valko" wrote: Is a data validation drop down list. Try this: =SUMPRODUCT((A1:A100="Netherlands"),(B1:B10="July")) Better to use cells to hold the criteria. D1 = Netherlands E1 = July =SUMPRODUCT((A1:A100=D1),(B1:B100=E1))  Biff Microsoft Excel MVP "Antonella" wrote in message ... Is a data validation drop down list. Can be the reason why does not work? "T. Valko" wrote: In both columns data must be chosen from a drop down menu Is that a data validation drop down list (or maybe a combo box) or is it an Auto Filter drop down?  Biff Microsoft Excel MVP "Antonella" wrote in message ... Hi, I'm hoping that someone can help me.. I am trying to count how many export has been done for each Region for July, how many for August and so on. I'll try to make myself a bit clear. For example: My first column (A1:A100) contains 10 different Regions (Netherlands, Italy, Spain etc). The second column (B1:B100) contains months ( July, August, September, October etc). I would like to know if there is formula to count how many times Netherlands July appears in those 2 columns. In both columns data must be chosen from a drop down menu.. Hope I made myself clear.. Can this be done ? Thanks Antonella "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 
#25




How can I count items in a filtered list?
Amazing! it works thank you!!!
Many Thanks Dominic "Domenic" wrote: Try removing the quotes from the second criteria... =SUMPRODUCT((B1:B391="Con"),(K1:K391=1))  Domenic Microsoft Excel MVP www.xlcentral.com Your Quick Reference to Excel Solutions In article , Dominic_gates wrote: Hi, I wonder if someone can help me, I am trying to count the number of individuals in two seperate columns on the same spreadsheet based on values that are text and numbers, i.e. "in column B1:B391 which are 'Con'" and "in column K1:K391 which have a value of '1'". i have tried the formula suggested below: =SUMPRODUCT((A1:A100="Netherlands"),(B1:B10="July")) adapting this to: =SUMPRODUCT((B1:B391="Con"),(K1:K391="1")) however this does not work. Any help would be amazing Many Thanks Dominic "T. Valko" wrote: Is a data validation drop down list. Try this: =SUMPRODUCT((A1:A100="Netherlands"),(B1:B10="July")) Better to use cells to hold the criteria. D1 = Netherlands E1 = July =SUMPRODUCT((A1:A100=D1),(B1:B100=E1))  Biff Microsoft Excel MVP "Antonella" wrote in message ... Is a data validation drop down list. Can be the reason why does not work? "T. Valko" wrote: In both columns data must be chosen from a drop down menu Is that a data validation drop down list (or maybe a combo box) or is it an Auto Filter drop down?  Biff Microsoft Excel MVP "Antonella" wrote in message ... Hi, I'm hoping that someone can help me.. I am trying to count how many export has been done for each Region for July, how many for August and so on. I'll try to make myself a bit clear. For example: My first column (A1:A100) contains 10 different Regions (Netherlands, Italy, Spain etc). The second column (B1:B100) contains months ( July, August, September, October etc). I would like to know if there is formula to count how many times Netherlands July appears in those 2 columns. In both columns data must be chosen from a drop down menu.. Hope I made myself clear.. Can this be done ? Thanks Antonella "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 
#26




How can I count items in a filtered list?
"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 The use of the "sumproduct" formula confuses me and how it's applied. I have a report at work that lists a number of categories for multiple people. With the use of the "countif" formula I'm able to identify the quantity associated with each category for the entire group. However, I want to filter down to a particular individual and have the quantities now only apply to that individual. Is there a way to combine the "countif" and "subtotal" formulas to make this happen? 
#27




How can I count items in a filtered list?
Found this through Excel help  exactly what I wanted, and in less than 5
mins too :) Thanks very much "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 
#28




How can I count items in a filtered list?

#29




How can I count items in a filtered list?
It works for me, too! Thanks very much for your help!
Also one quick note for other people trying to count TRUE or FALSE values, I had to remove the double quotes  apparently Excel see's them as special values, even though it doesn't seem to convert them to numbers (the way a database does). This worked for counting the number of TRUE's in a filtered list: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)ROW(B2),0,1)),(B2:B100=TRUE)) "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 
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) 