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

Pitmo

New Member
Joined
Jan 31, 2017
Messages
10
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
8,984
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
10
Cracking, Thanks Eric - that has indeed worked.

Much appreciated.
 

Forum statistics

Threads
1,082,175
Messages
5,363,564
Members
400,750
Latest member
007432

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top