COUNTIF - Accounting for mixed duplicates

[Harveux]

Hi I need a formula that counts cells beyond the formula =COUNTIFS(A:A,"Blue",D:D,"On time") that accounts for duplicates. Any help would be appreciated =)

Row 2 (Blue/Unique#/Late) should be counted in H2

Row 3 (Blue/Unique#/On time) should be counted in G2

Row 4 and 5 (Blue/Duplicate#/One Late, One On Time) should be counted once in J2

Row 6 and 7 (Blue/Duplicate#/Both on time) should be counted in G2

Row 8 and 9 (Red/Duplicate#/Both on time) should be counted in G3

Row 10 (Green/Unique#/Not Due) should be counted in I4

 

[Harveux]

Dropbox - Formula Resolution.jpg

 

[CyrusTheVirus]

what are your expected answers?

Welcome to the forums.

 

[Harveux]

Thanks for the welcome,

My expected results are shown here (manually inserted) Dropbox - Formula Resolution2.jpg
Of which the bar chart draws its input.
I have a larger data set (not shown) that I need to automatically assign into the second section (2) which measures occurrences. Where there is duplicate numbers in the C column I want to only assign into 'Late' or 'On time' if both duplicates show 'Late' or 'On Time'. If they show different things i.e. one shows late and the other on time, I want it to be counted as 'mixed' and not into the late or on time columns in 2. Unique non duplicates should still be counted as normal in the second section i.e. late counted in late column.

I hope this is clear, if not I will try and explain it better.

Many thanks