1

I have three columns: ID, events, and month. I need to get the count of events by month unique by ID.

So far I have the count of events by month (e.g. 1806 unique logins in May) using CountIfs(Range("B2:B276609"), EventName, Range(C2:C76602"), m)).

How do I filter this above count so only the unique IDs within that count is being used? Note that I have to loop this through a bunch of event types and months.

To make this clearer, let me provide some sample data:

ID  Event  Month
1   Login  May 16
2   click  July 16
1   Save   June 16
1   Login  May 16
3   Save   June 16

From this I need to get the following info:

1 unique login in May 16
2 unique saves in June 16
1 unique click in July 16
ysrome
  • 71
  • 1
  • 9
  • This solution allows you to get all unique values from a table: http://stackoverflow.com/questions/1429899/getting-unique-values-in-excel-by-using-formulas-only If you require *unique* values based on two columns then you can (1) create a helper column which combines the two columns or (2) combine the columns in the formula with an array formula before comparing the values for their uniqueness. – Ralph Jun 14 '16 at 22:54
  • r u interested in a way to do it without vba? if so what version of excel do you have? – chungtinhlakho Jun 14 '16 at 23:25
  • 1
    This is a simple aggregate SQL query which **can** be run with Excel VBA, calling ADO on the current workbook. No nested loops or if/then conditionals needed. – Parfait Jun 15 '16 at 02:17

2 Answers2

1

Based on the link in Ralph's comments which can be found here here you get:

To know how many unique items you have you can use this regular formula:

=SUM(IF(FREQUENCY(COUNTIF(Colors,"<"&Colors),COUNTIF(Colors,"<"&Colors)),1))

I then extended this to multiple columns just change the countif formulas to countifs. (different ranges used obviously).

=SUM(IF(FREQUENCY(COUNTIFS($A$1:$A$10,"<"&$A$1:$A$10,$B$1:$B$10,"<"&$B$1:$B$10),COUNTIFS($A$1:$A$10,"<"&$A$1:$A$10,$B$1:$B$10,"<"&$B$1:$B$10)),1))
Community
  • 1
  • 1
gtwebb
  • 2,981
  • 3
  • 13
  • 22
1

You can use Excel's inbuilt remove duplicates function. 

ActiveSheet.Range("A2:C76602").RemoveDuplicates Columns:=Array(1,2), Header:=xlYes
Yarnspinner
  • 852
  • 5
  • 7