# Count different values for another value

• I have a list of application with customer names and their phone numbers. The reality is that some people reapply with the same phone number, others using a different phone number and - contrary - some phones are shared by two or more people. Thus I'm having a list like that:

[TABLE="width: 128"]

[tr]

[TD="width: 64"]Col. A
[/TD]
[TD="width: 64"]Col. B
[/TD]

[/tr]

[tr]

[td]

Kowalski

[/td]

[TD="align: right"]156[/TD]

[/tr]

[tr]

[td]

Nowak

[/td]

[TD="align: right"]625[/TD]

[/tr]

[tr]

[td]

Malinowski

[/td]

[TD="align: right"]259[/TD]

[/tr]

[tr]

[td]

Kowalski

[/td]

[TD="align: right"]325[/TD]

[/tr]

[tr]

[td]

Malinowski

[/td]

[TD="align: right"]259[/TD]

[/tr]

[tr]

[td]

Jaruzelski

[/td]

[TD="align: right"]223[/TD]

[/tr]

[/TABLE]

Would someone please suggest a function to count number of phones used by a single name? I've tried to do it with pivot table (names in rows and sum of phone instances) but it doesn't seem to work, as it counts numbers regardless of the fact they are the same.

• Re: Count different values for another value

use this data starting from A2

=IF(A2=\$A\$2:\$A\$8,COUNTIF(\$B\$2:\$B\$8,\$B\$2:\$B\$8),)

• Re: Count different values for another value

Hmmmmm... But that will give me just the number of instances where both name and phone address are the same, not different.
Thank you anyway!

• Re: Count different values for another value

another one

=IF(E2=\$E\$2:\$E\$8,MAX(COUNTIF(\$F\$2:\$F\$8,\$F\$2:\$F\$8),COUNTIF(\$E\$2:\$E\$8,\$E\$2:\$E\$8)),)

• Re: Count different values for another value

I guess it returns just the same as previous formula, as it simply provides maximum value for phone numbers used

I've managed to get what I wanted but with much more work - counting the name/phone pairs and then filtering out those for which the value for name was greater than 1. Too much manual work, though....

• Re: Count different values for another value

It still involves extra data, but you could add this formula to column C

=--(COUNTIFS(\$A\$2:A2,A2,\$B\$2:B2,B2)=1)

and then create a pivot table with the names in the Row field and the sum of the new column as the Data field.

[COLOR="Purple"]Regards,[/COLOR]
[COLOR="Purple"]Batman.[/COLOR]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!