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

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

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

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

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

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