Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 questions regarding count formula
If I have the following data
A B C 1 Personnel group type p't number 2 Melinda 1 4 3 Sean 1 5 4 Melinda 1 6 5 Sean 2 30 6 Fay 3 8 7 Melinda 1 32 1. How shall I contruct my formula to count the number of data that satisfy the following criteria: 1. personnel is Melinda 2. group type is 1 3. p't number is between 4 and 8 2. When designing the formula, can the formula be extended in such a way that even if I add more data to the table above, I can still get an accurate count without redefining my data range in the formula? Thanks. Melinda |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 questions regarding count formula
Hi Melinda,
You can use the sumproduct function with a range that is sufficient to include new data e.g. =SUMPRODUCT(--(A2:A200="Melinda"),--(B2:B200=1),--(C2:C200=4),--(C2:C200<=8)) Hth Anthony "Melinda" wrote: If I have the following data A B C 1 Personnel group type p't number 2 Melinda 1 4 3 Sean 1 5 4 Melinda 1 6 5 Sean 2 30 6 Fay 3 8 7 Melinda 1 32 1. How shall I contruct my formula to count the number of data that satisfy the following criteria: 1. personnel is Melinda 2. group type is 1 3. p't number is between 4 and 8 2. When designing the formula, can the formula be extended in such a way that even if I add more data to the table above, I can still get an accurate count without redefining my data range in the formula? Thanks. Melinda |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 questions regarding count formula
Thanks Anthony for your prompt reply. However, this formula doesn't work.
Using the table below to test, the formula returns 0 instead of 2. Any suggestions? Melinda "Anthony D" wrote: Hi Melinda, You can use the sumproduct function with a range that is sufficient to include new data e.g. =SUMPRODUCT(--(A2:A200="Melinda"),--(B2:B200=1),--(C2:C200=4),--(C2:C200<=8)) Hth Anthony "Melinda" wrote: If I have the following data A B C 1 Personnel group type p't number 2 Melinda 1 4 3 Sean 1 5 4 Melinda 1 6 5 Sean 2 30 6 Fay 3 8 7 Melinda 1 32 1. How shall I contruct my formula to count the number of data that satisfy the following criteria: 1. personnel is Melinda 2. group type is 1 3. p't number is between 4 and 8 2. When designing the formula, can the formula be extended in such a way that even if I add more data to the table above, I can still get an accurate count without redefining my data range in the formula? Thanks. Melinda |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 questions regarding count formula
Using the table below to test, the formula returns 0 instead of 2.
Anthony's suggestion should work. The problem lies in your data. There may be text numbers in the numerics in cols B & C, and/or there may be extra white spaces in the names in col A. Any of these could throw the matching off. This tweak should cover all possibilities: =SUMPRODUCT(--(TRIM(A2:A200)="Melinda"),--(B2:B200+0=1),--(C2:C200+0=4),--(C2:C200+0<=8)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Melinda" wrote: Thanks Anthony for your prompt reply. However, this formula doesn't work. Using the table below to test, the formula returns 0 instead of 2. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 questions regarding count formula
Thanks. It solve the first part of my question. However, do I have to
define the row number in my formula in order to let it work? Can I just define the column which I want instead? Will this results in changing the formula? Melinda "Max" wrote: Using the table below to test, the formula returns 0 instead of 2. Anthony's suggestion should work. The problem lies in your data. There may be text numbers in the numerics in cols B & C, and/or there may be extra white spaces in the names in col A. Any of these could throw the matching off. This tweak should cover all possibilities: =SUMPRODUCT(--(TRIM(A2:A200)="Melinda"),--(B2:B200+0=1),--(C2:C200+0=4),--(C2:C200+0<=8)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Melinda" wrote: Thanks Anthony for your prompt reply. However, this formula doesn't work. Using the table below to test, the formula returns 0 instead of 2. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 questions regarding count formula
The ranges need to be defined and should be consistent in structure, as per
the example suggested. SUMPRODUCT doesn't accept entire col references, eg: A:A, B:B. In defining the ranges, try to use the smallest possible range sizes, otherwise recalc performance would be slow. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Melinda" wrote in message ... Thanks. It solve the first part of my question. However, do I have to define the row number in my formula in order to let it work? Can I just define the column which I want instead? Will this results in changing the formula? Melinda |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 questions regarding count formula
what does the "--" mean? I found that if I remove that from my formula, it
will not work. Does it have special meaning? Melinda "Anthony D" wrote: Hi Melinda, You can use the sumproduct function with a range that is sufficient to include new data e.g. =SUMPRODUCT(--(A2:A200="Melinda"),--(B2:B200=1),--(C2:C200=4),--(C2:C200<=8)) Hth Anthony "Melinda" wrote: If I have the following data A B C 1 Personnel group type p't number 2 Melinda 1 4 3 Sean 1 5 4 Melinda 1 6 5 Sean 2 30 6 Fay 3 8 7 Melinda 1 32 1. How shall I contruct my formula to count the number of data that satisfy the following criteria: 1. personnel is Melinda 2. group type is 1 3. p't number is between 4 and 8 2. When designing the formula, can the formula be extended in such a way that even if I add more data to the table above, I can still get an accurate count without redefining my data range in the formula? Thanks. Melinda |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 questions regarding count formula
The double unary "--" gently coerces the TRUEs/FALSEs returned by the
various match expressions (eg: TRIM(A2:A200)="Melinda") into numeric 1's/0's. A variation of the earlier expression (without using "--") which would work equally well here is: =SUMPRODUCT((TRIM(A2:A200)="Melinda")*(B2:B200+0=1 )*(C2:C200+0=4)*(C2:C200+0<=8)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Melinda" wrote in message ... what does the "--" mean? I found that if I remove that from my formula, it will not work. Does it have special meaning? Melinda |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 questions regarding count formula
additional details he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html "Melinda" wrote: what does the "--" mean? I found that if I remove that from my formula, it will not work. Does it have special meaning? Melinda "Anthony D" wrote: Hi Melinda, You can use the sumproduct function with a range that is sufficient to include new data e.g. =SUMPRODUCT(--(A2:A200="Melinda"),--(B2:B200=1),--(C2:C200=4),--(C2:C200<=8)) Hth Anthony "Melinda" wrote: If I have the following data A B C 1 Personnel group type p't number 2 Melinda 1 4 3 Sean 1 5 4 Melinda 1 6 5 Sean 2 30 6 Fay 3 8 7 Melinda 1 32 1. How shall I contruct my formula to count the number of data that satisfy the following criteria: 1. personnel is Melinda 2. group type is 1 3. p't number is between 4 and 8 2. When designing the formula, can the formula be extended in such a way that even if I add more data to the table above, I can still get an accurate count without redefining my data range in the formula? Thanks. Melinda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to count numbers | Excel Worksheet Functions | |||
Using the $ in a count formula | Excel Discussion (Misc queries) | |||
Count If Formula | Excel Discussion (Misc queries) | |||
Formula format for Count or Countif funtion with two criterias | Excel Worksheet Functions | |||
Count If Formula | Excel Worksheet Functions |