Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Formula
Hi everybody,
I need your help in formula that counts a group of the same data as one while having different info in the next column: It is important that it should count based on column B or C but not A amd if no info in front of a name then it should not count. The results of each column must be col B = 3, col C = 4. Please help. Thanks. col A col B col C Jack 10 20 Jack 20 10 Jack 45 15 Mary 15 Mary 16 Mary 9 Sam 80 15 Sam 45 12 Sam 15 9 Sam 10 9 Max 10 Max 11 Max 88 Alex 15 Alex 10 Alex 9 Count: 3 4 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Formula
It is difficult to understand what you are counting. How are you getting
your results of 3 and 4? Regards, Paul -- "alish" wrote in message ... Hi everybody, I need your help in formula that counts a group of the same data as one while having different info in the next column: It is important that it should count based on column B or C but not A amd if no info in front of a name then it should not count. The results of each column must be col B = 3, col C = 4. Please help. Thanks. col A col B col C Jack 10 20 Jack 20 10 Jack 45 15 Mary 15 Mary 16 Mary 9 Sam 80 15 Sam 45 12 Sam 15 9 Sam 10 9 Max 10 Max 11 Max 88 Alex 15 Alex 10 Alex 9 Count: 3 4 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Formula
The records are group counted. For column B there are three records for
"Jack", "Mary" and "Sam" only. But column C has four records: Jack, Sam, Max and Alex. Thank you for your response. "PCLIVE" wrote: It is difficult to understand what you are counting. How are you getting your results of 3 and 4? Regards, Paul -- "alish" wrote in message ... Hi everybody, I need your help in formula that counts a group of the same data as one while having different info in the next column: It is important that it should count based on column B or C but not A amd if no info in front of a name then it should not count. The results of each column must be col B = 3, col C = 4. Please help. Thanks. col A col B col C Jack 10 20 Jack 20 10 Jack 45 15 Mary 15 Mary 16 Mary 9 Sam 80 15 Sam 45 12 Sam 15 9 Sam 10 9 Max 10 Max 11 Max 88 Alex 15 Alex 10 Alex 9 Count: 3 4 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Formula
Assuming there are no empty cells within the name range in col A.
Try this array formula** : =COUNT(1/FREQUENCY(IF(B2:B17<"",MATCH($A2:$A17,$A2:$A17,0) ),ROW(A2:A17)-MIN(ROW(A2:A17))+1)) Copy across ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "alish" wrote in message ... Hi everybody, I need your help in formula that counts a group of the same data as one while having different info in the next column: It is important that it should count based on column B or C but not A amd if no info in front of a name then it should not count. The results of each column must be col B = 3, col C = 4. Please help. Thanks. col A col B col C Jack 10 20 Jack 20 10 Jack 45 15 Mary 15 Mary 16 Mary 9 Sam 80 15 Sam 45 12 Sam 15 9 Sam 10 9 Max 10 Max 11 Max 88 Alex 15 Alex 10 Alex 9 Count: 3 4 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Formula
Thanks, Valko! It worked! Appreciate your help!
"T. Valko" wrote: Assuming there are no empty cells within the name range in col A. Try this array formula** : =COUNT(1/FREQUENCY(IF(B2:B17<"",MATCH($A2:$A17,$A2:$A17,0) ),ROW(A2:A17)-MIN(ROW(A2:A17))+1)) Copy across ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "alish" wrote in message ... Hi everybody, I need your help in formula that counts a group of the same data as one while having different info in the next column: It is important that it should count based on column B or C but not A amd if no info in front of a name then it should not count. The results of each column must be col B = 3, col C = 4. Please help. Thanks. col A col B col C Jack 10 20 Jack 20 10 Jack 45 15 Mary 15 Mary 16 Mary 9 Sam 80 15 Sam 45 12 Sam 15 9 Sam 10 9 Max 10 Max 11 Max 88 Alex 15 Alex 10 Alex 9 Count: 3 4 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Formula
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "alish" wrote in message ... Thanks, Valko! It worked! Appreciate your help! "T. Valko" wrote: Assuming there are no empty cells within the name range in col A. Try this array formula** : =COUNT(1/FREQUENCY(IF(B2:B17<"",MATCH($A2:$A17,$A2:$A17,0) ),ROW(A2:A17)-MIN(ROW(A2:A17))+1)) Copy across ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "alish" wrote in message ... Hi everybody, I need your help in formula that counts a group of the same data as one while having different info in the next column: It is important that it should count based on column B or C but not A amd if no info in front of a name then it should not count. The results of each column must be col B = 3, col C = 4. Please help. Thanks. col A col B col C Jack 10 20 Jack 20 10 Jack 45 15 Mary 15 Mary 16 Mary 9 Sam 80 15 Sam 45 12 Sam 15 9 Sam 10 9 Max 10 Max 11 Max 88 Alex 15 Alex 10 Alex 9 Count: 3 4 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Formula - Count Ticks | Excel Discussion (Misc queries) | |||
COUNT formula help | Excel Worksheet Functions | |||
Trying to construct a count count formula | Excel Discussion (Misc queries) | |||
Count Formula | Excel Discussion (Misc queries) | |||
Count Formula | Excel Worksheet Functions |