Duplicates, SUMPRODUCT and COUNTIF

[Harveux]

Hi guys, any help on this would be appreciated =) Hope I can help others at some point too!

I am creating a count separated into 3 categories: 'Late', 'Not Late', and 'Mix'

Where exact duplicates exist I want them to be counted once in 'Late' or 'Not Late' (depending on the status of duplicate)
When a duplicate has two different statuses (i.e. Late and Not Late) I want them to be counted in 'Mix'
Unique ID's should have their statuses counted into their respective groups.

The formula I have so far for the 'late' column is:

=SUMPRODUCT(((B2:B16="late")*(C2:C16="blue"))/(COUNTIFS(A2:A16, A2:A16, B2:B16, "late", C2:C16, "blue")+((B2:B16<>"late")+(C2:C16<>"blue"))))

This is handy if there are exact duplicates, but I need it to exclude from the count all duplicates under one ID if one of the rows contains a different status.

Top left is cell A1
Result should be
Blue:
Late: 1
On Time: 1
Mixed: 1
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Status[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]1423[/TD]
[TD]Late[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]1423[/TD]
[TD]Late[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]1423[/TD]
[TD]Late[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]5124[/TD]
[TD]Late[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]5124[/TD]
[TD]Late[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]2341[/TD]
[TD]On Time[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]On Time[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]1212[/TD]
[TD]On Time[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]1212[/TD]
[TD]Late[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]Late[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]5124[/TD]
[TD]Late[/TD]
[TD]Red[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

What constitutes an "exact duplicate" ?

Just the ID repeating or ID AND category ?

And what are you expecting your output to look like based on that data?

 

[jorismoerings]

Hi,

try this as possible solution:

Excel 2016 (Windows) 64 bit
	A	B	C
1	ID	Status	Category
2	1423	Late	Blue
3	1423	Late	Blue
4	1423	Late	Blue
5	5124	Late	Red
6	5124	Late	Red
7	2341	On Time	Blue
8	1111	On Time	Red
9	1212	On Time	Blue
10	1212	Late	Blue
11	1111	Late	Red
12	5124	Late	Red
13
14
15	Blue	Late	1
16		On Time	1
17		Mixed	1
Sheet1

Cell Formulas
Range	Formula
C15	{=SUM(--(FREQUENCY(IF((C2:C12=A15)*(B2:B12=B15),MATCH(B2:B12,B2:B12,0)),ROW(B2:B12)-ROW(B2)+1)>0))}
C16	{=SUM(--(FREQUENCY(IF((C2:C12=A15)*(B2:B12="On Time"),MATCH(B2:B12,B2:B12,0)),ROW(B2:B12)-ROW(B2)+1)>0))}
C17	{=SUM(--(FREQUENCY(IF((C2:C12=A15),MATCH(A2:A12,A2:A12,0)),ROW(B2:B12)-ROW(B2)+1)>0))-C16-C15}
Press CTRL+SHIFT+ENTER to enter array formulas.