Count Formula

[alish]

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

 

[PCLIVE]

It is difficult to understand what you are counting. How are you getting
your results of 3 and 4?

Regards,
Paul

 

[alish]

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.

 

[T. Valko]

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)

 

[alish]

Thanks, Valko! It worked! Appreciate your help!

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)

 

[T. Valko]

You're welcome. Thanks for the feedback!