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

[Pitmo]

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.

ID	Email	<help> Count of Unique Email Addresses</help>
101	101a@excelquery.com	1
101	101a@excelquery.com	1
102	102a@excelquery.com	2
102	102b@excelquery.com	2
103	103a@excelquery.com	1
104	104a@excelquery.com	1
105	105a@excelquery.com	1
106	106a@excelquery.com	2
106	106a@excelquery.com	2
106	106b@excelquery.com	2
107	107a@excelquery.com	1

<tbody>
</tbody>

 

[Eric W]

Try:

	G	H	I
1	ID	Email	Count of Unique Email Addresses
2	101	101a@excelquery.com	1
3	101	101a@excelquery.com	1
4	102	102a@excelquery.com	2
5	102	102b@excelquery.com	2
6	103	103a@excelquery.com	1
7	104	104a@excelquery.com	1
8	105	105a@excelquery.com	1
9	106	106a@excelquery.com	2
10	106	106a@excelquery.com	2
11	106	106b@excelquery.com	2
12	107	107a@excelquery.com	1

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

Sheet8​

Array Formulas Cell Formula 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]

Cracking, Thanks Eric - that has indeed worked.

Much appreciated.

 

[Eric W]

Glad to help, thanks for the feedback.