Couting Duplicate Values as 1 and summing total

[rslush91]

I have a large spreadsheet in which we are trying to track how many times a partiulcar store submits products to our department over the course of a year. Each product has a date assigned to it and from this we assign a Store/Date concatenate to it to have a unique identifier. There are times where there are multiple submissions on the same date and this creates duplicates to appear. We want to count these duplicates as 1 and then sum how many times this store appears in a file. I have attached a basic sample of what you would see.

In the file you will see that there are multiple duplicates in column D and I need to count each duplicate as 1 and them sum the 1s to get the total number of submissions per store. My file I am working with is obviously bigger but would be too big to submit on the forum.

Any help would be greatly appreciate

[tim201110]

=--(COUNTIF($D$3:D3,D3)=1)
and 2 way subtotal and sumifs

[FlameRetired]

Perhaps another way. Copy/paste Store# (like column F below) > remove duplicates and then in G2 this and filled down.

Please Login or Register  to view this content.

There are also formula methods for removing duplicates. The Remove Duplicates feature is simpler and without calculation overhead.

	A	B	C	D	E	F	G
1	Date	Store	Product	Store Date		Store	Counts
2	1/1/2018	1234	A	123443101		1234	2
3	2/18/2018	7890	Z	789043149		7890	3
4	2/18/2018	7890	Y	789043149
5	2/18/2018	7890	X	789043149
6	3/1/2018	1234	B	123443160
7	3/1/2018	1234	C	123443160
8	6/5/2018	7890	M	789043256
9	7/15/2018	7890	N	789043296

Does this help?