Count duplicates when there is more than one

[RSnap3232]

hello all,

after a formula that can work on the below example, I am working on putting together a report where there will be a list of locations and then next to that will be a name, I only need to know when a name is duplicated and how many duplications there are, as you can see below I have Bristol that is mentioned four times with two different names so the result would need to be one, whereas Swansea is mentioned four times but only one name is mentioned more than once so the result would be one, is this possible?

 

[hagia_sofia]

Hello, how the count is supposed to proceed when a combination of the same location and name is mentioned more than 2 times?

 

[RSnap3232]

hello all,

apologies was a mistake on original post

after a formula that can work on the below example, I am working on putting together a report where there will be a list of locations and then next to that will be a name, I only need to know when a name is duplicated and how many duplications there are, as you can see below I have Bristol that is mentioned four times with two different names so the result would need to be two, whereas Swansea is mentioned four times but only one name is mentioned more than once so the result would be one, is this possible?
View attachment 120674

 

[RSnap3232]

Hello, how the count is supposed to proceed when a combination of the same location and name is mentioned more than 2 times?

apologies not sure what your asking for, i'm just curious if its possible to do a count for each location but only if the same name is mentioned more than once without the need of putting in what the name could be (as there could be a large number of names).

 

[hagia_sofia]

Hello, the question was if e.g. John Smith is mentioned 4 times in relation to Bristol whether it does count as 1 case of a duplicity, i.e. irrespective of the number of times he is mentioned. The option below which relies on two helper columns (columns R and S) does so:

=IF(COUNTIFS($P$7:$P$18,P7,$Q$7:$Q$18,Q7)>1,1,0)

=IFERROR(R7/COUNTIFS($P$7:$P$18,P7,$Q$7:$Q$18,Q7,$R$7:$R$18,1),0)

=SUMIFS($S$7:$S$18,$P$7:$P$18,U7)

 

[RSnap3232]

Hello, the question was if e.g. John Smith is mentioned 4 times in relation to Bristol whether it does count as 1 case of a duplicity, i.e. irrespective of the number of times he is mentioned. The option below which relies on two helper columns (columns R and S) does so:

=IF(COUNTIFS($P$7:$P$18,P7,$Q$7:$Q$18,Q7)>1,1,0)

=IFERROR(R7/COUNTIFS($P$7:$P$18,P7,$Q$7:$Q$18,Q7,$R$7:$R$18,1),0)

=SUMIFS($S$7:$S$18,$P$7:$P$18,U7)

that is brilliant thank you kindly.