If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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 |
#22
|
|||
|
|||
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.xl-central.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 |
#23
|
|||
|
|||
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.xl-central.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 |
#24
|
|||
|
|||
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? |
#25
|
|||
|
|||
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 |
#26
|
|||
|
|||
How can I count items in a filtered list?
|
#27
|
|||
|
|||
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 |
#29
|
|||
|
|||
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 |
#30
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|