Max formula returning 0 based on values from another formula-CodePudding

I am trying to make the following work:

A               B   C   D       
BK-8811436091   57  1   Unique      
BK-8811436091   57  1   Duplicate       
BK-8811436091   57  1   Duplicate       
BK-8811436091   58  1   Unique      
BK-8811436091   57  1   Duplicate       
BK-8811436091   59  1   Unique      
BK-8811436091   57  1   Duplicate       
BK-8811436091   58  1   Duplicate       
BK-8811436092   54  1   Unique      
BK-8811436092   56  1   Unique      
BK-8811436092   58  1   Unique      
BK-8811436092   57  1   Unique      
BK-8811436091   57  1   Duplicate       
BK-8811436091   58  1   Duplicate       
BK-8811436092   57  1   Duplicate

If column A B have a unique combination it should give 1 in column C
If column A B have a duplicate it should receive the same number as it did before (e.g. row 2 should be 1 in column C)
if column A is equal but column B is different, the number in column C should get 1 (e.g. row 4 should be 2 in column C and consequently a 3 in row 6)
if there is an entirely unique column A B this should get a 1 again (e.g. row 9 should get a 1).

I have been attempting this with the following formula in column C (which gives wrong answers right now):

=IF(AND(D2="unique";A2&B2<>A1&B1);B2-B2 1;IF(AND(D2="Duplicate";A2&B2=A1&B1);MAX(IF($A$2:A2=A2;$C$2:C2));IF(AND(A2&B2<>A1&B1;D2="unique");MAX(IF($A$2:A2=A2;$C$2:C2) 1);MAX(IF($A$2:A2=A2;$C$2:C2)))))

I don't think it's entirely correct yet but I feel I should be able to manage it with this train of thought. One of the problems that are occuring is that the max formula is returning 0 values even though there are quite obviously 1's in column C --> max returning 1 1 should lead to two.

Does anyone know what I am doing wrong here? I am no programmer / coder so please correct me if my formatting of this question is wrong.

Kind regards,

VHes

CodePudding user response：

This is my approach to find duplicates & unique values., based on your excel versions you can choose the formulas & If you have large set of data, CountIf is not the best option. Instead, use Power Query.

CodePudding user response：

=SUMPRODUCT(($A$1:A1=A1)*($D$1:D1="Unique")) In C1 dragged down.

CodePudding user response：

Place this in C1:

=SUMPRODUCT((A1=A$1:A1)*(B1=B$1:B1))

Fill/copy/paste it down.

It is easier to say "I am the 4th instance" than it is to say "let me find the previous instance and increment whatever its count was".