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,122
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,084,736
Messages
5,379,513
Members
401,608
Latest member
nanana2020

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top