Unique counts

[vkorla]

In this xlsx (link: https://drive.google.com/open?id=1agN53t1bX8qxCpFGqTOAdHIV_tcBW8ih), I want to find out the following.

For each item listed, I want to know a unique count based on month-year. I.e., for item 4656541653, the count for January 2017 would = 1, December 2017 = 1, November 2017 = 1, even though it appears 3 times in November 2017. Similarly, item 465645 would appear once under the month-year of March 2017, February 2017 and January 2017 even though it appears twice under January and February 2017

How can I do this? Do i need to add a helper column prior to making a pivot table?

The full data set contains thousands of records so I'm not sure of a quick way to achieve this. Thanks everyone!

 

[AhoyNC]

Here is one way using a helper column:
Copy formula in D2 down as needed.
Copy formula in G3 down and across as needed.
Change ranges to match your data.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Item	Date	Month-Year	Unique ?
2	95299	1/26/2017 20:35	January 2017	TRUE		Item	January 2017	February 2017	March 2017	April 2017
3	4656451653	12/14/2017 10:00	December 2017	TRUE		95299	1	0	0	0
4	4656451653	11/30/2017 13:09	November 2017	TRUE		4656451653	0	0	0	0
5	4656451653	11/16/2017 11:18	November 2017	FALSE		465645	1	1	1	0
6	4656451653	11/2/2017 12:45	November 2017	FALSE		8976541	0	0	0	1
7	465645	3/9/2017 16:30	March 2017	TRUE
8	465645	2/23/2017 17:00	February 2017	TRUE
9	465645	2/9/2017 17:10	February 2017	FALSE
10	465645	1/26/2017 20:35	January 2017	TRUE
11	465645	1/12/2017 15:40	January 2017	FALSE
12	8976541	11/30/2017 12:00	November 2017	TRUE
13	8976541	11/16/2017 12:36	November 2017	FALSE
14	8976541	11/2/2017 13:09	November 2017	FALSE
15	8976541	10/19/2017 14:24	October 2017	TRUE
16	8976541	10/19/2017 10:00	October 2017	FALSE
17	8976541	10/5/2017 13:04	October 2017	FALSE
18	8976541	9/21/2017 11:00	September 2017	TRUE
19	8976541	9/7/2017 10:00	September 2017	FALSE
20	8976541	8/24/2017 16:05	August 2017	TRUE
21	8976541	8/10/2017 13:05	August 2017	FALSE
22	8976541	7/13/2017 13:10	July 2017	TRUE
23	8976541	6/29/2017 12:56	June 2017	TRUE
24	8976541	6/15/2017 13:05	June 2017	FALSE
25	8976541	6/1/2017 13:00	June 2017	FALSE
26	8976541	5/18/2017 13:05	May 2017	TRUE
27	8976541	5/4/2017 12:55	May 2017	FALSE
28	8976541	4/20/2017 12:55	April 2017	TRUE
Sheet

 

[István Hirsch]

Try this (enter into D2 and copy down):

=COUNTIFS($A$2:$A$20,A2,$C$2:$C$20,C2)

 

[admiral100]

Another approach :

D2 =1/COUNTIFS($A$2:$A$28,A2,$C$2:$C$28,C2)

 

[Fazza]

With lots of data, a pivot table offers advantages. A helper column is not needed (and it will be faster without). You can even delete the current third column - so if that has a formula, it will be faster again.

NOTE : the offered solution modifies the dataset en route to making the pivot table.

I'll assume there are just the numbers in column A and dates/times in column B. Give the data a defined name, say MyData. Save the file. Start the external data wizard (I'll explain it this way cause I think it works on all Excel versions since pivot tables started in ~1995), such as ALT-D-D-N. Follow the wizard to the end selecting the file and then data on the way. At the last step, choose the option to edit in MS Query. Via the SQL button replace the text by

SELECT DISTINCT MyData.Item, INT(MyData.Date) - Day(MyData.Date) + 1 AS [MyDate]
FROM .MyData MyData

OK to enter, and OK to acknowledge a message about not being able to graphically represent. Then choose the option to make a pivot table as you finish using MS Query. Then set the pivot table how you want it.

cheers