Count unique duplicates, based off IF

[hambly]

See attached example. (can't attach) Maybe it did attach after all when I dragged to the window...)

I'm looking to count how many duplicate entries columns B and C have based off column A.

Eg A is 'rob'. Columns B and C have an entry each with '[email protected]' so the result would be 2 (ideally it would be 1 as that is 1 unique duplicate, but not sure if that's possible?) To complicate, if there's more than 2 of the same entry, I'd like it to ignore more than 2... So if '[email protected]' was listed 4 times total in B and C, the result would still be 2 (or ideally, 1!)

Not sue if a pivot table would work?

(Ok, I can't seem to add attachments? I press the 'Attachments' button and it just comes up with a thin white bar. Tried in Safari and Chrome...)

Thanks
Rob

[dflak]

I'm not sure I am getting your logic here. Rob has three unique entries: [email protected], info, and [email protected].

[hambly]

Column B and C (combined) have 2 '[email protected] and 2 '[email protected]'

What I'd like the result to be is 2. 2 sets of duplicates were found in B and C.

[FlameRetired]

hambly,

Agree with dflak. I'll hazard a guess. Try array entering this in H1

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.