Count the volume of Unique Results based on a condition (using structured references)

Pitmo

New Member
Joined
Jan 31, 2017
Messages
11
Hi, I have a list of Account User IDs (Column 1) and a list of email addresses(column 2) associated with that user. I need to count the number of unique email addresses per user and display this on each row (column 3).

The data I have is stored in tables so I need the formula to allow for Calculated fields. e.g. below:

Any help would be appreciated.

M.


IDEmail<help> Count of Unique Email Addresses</help>
101101a@excelquery.com1
101101a@excelquery.com1
102102a@excelquery.com2
102102b@excelquery.com2
103103a@excelquery.com1
104104a@excelquery.com1
105105a@excelquery.com1
106106a@excelquery.com2
106106a@excelquery.com2
106106b@excelquery.com2
107107a@excelquery.com1

<tbody>
</tbody>
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,155
Try:

GHI
1IDEmailCount of Unique Email Addresses
2101101a@excelquery.com1
3101101a@excelquery.com1
4102102a@excelquery.com2
5102102b@excelquery.com2
6103103a@excelquery.com1
7104104a@excelquery.com1
8105105a@excelquery.com1
9106106a@excelquery.com2
10106106a@excelquery.com2
11106106b@excelquery.com2
12107107a@excelquery.com1

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet8

Array Formulas
CellFormula
I2{=SUM(SIGN(FREQUENCY(IF([ID]=[@ID],MATCH(,[Email],0[/COLOR])[/COLOR]),ROW([COLOR=Purple][ID][/COLOR])-ROW([COLOR=Purple]INDEX([COLOR=Teal][ID],1[/COLOR])[/COLOR])+1[/COLOR])[/COLOR])[/COLOR])}[/TD]
[/TR]
</tbody>[/TABLE]
[B]Entered with Ctrl+Shift+Enter.[/B] If entered correctly, Excel will surround with curly braces {}.
[B]Note: Do not try and enter the {} manually yourself[/B][/TD]
[/TR]
</tbody>[/TABLE]

I set up G1:I12 as Table1.

<thead>
</thead><tbody>

<tbody>
 

Pitmo

New Member
Joined
Jan 31, 2017
Messages
11
Cracking, Thanks Eric - that has indeed worked.

Much appreciated.
 

Forum statistics

Threads
1,085,370
Messages
5,383,241
Members
401,820
Latest member
RustEE2020

Some videos you may like

This Week's Hot Topics

Top