SOLVED

Formula to count based on creteria but next creteria count should start from 2, instead of 1

Occasional Contributor

Hi,

 

I've a situation. below is a table in which I have put a count in running count using COUNTIFS($A$2:A2,A2,$B$2:B2,B2) and then nested the same in F column to uniquely identify the sku IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1,0,1), 

What i need is the desired output in column G, where if  my style code is repeating in different inventory location, then for first inventory location it gives me a count of 1, if same style code repeat in second inventory location then it says 2 ... (it should not say 1st). so basically i need to count the instance.

 

 

STYLECODE(A)BRANCH(B)Inventory Loc©Status(D)Running Count€Options(F)Desired Output(G)
12163741113192RT-KSA ONLINEactive111
12163741113192RT-KSA ONLINEactive200
12163741113800RT KSA - Ecomactive112
12163741113800RT KSA - Ecomactive200
12163741113800RT KSA - Ecomactive300
12163741113800RT KSA - Ecomactive400
12163741113192RT-KSA ONLINEactive300
12163741113191KSA POSactive113
5 Replies
best response confirmed by sherikhan88 (Occasional Contributor)
Solution

@sherikhan88 

This solution doesn't require those helper columns:

=IF(COUNTIFS($A$2:A2,A2,$C$2:C2,C2)=1,XMATCH(C2,UNIQUE(FILTER($C$2:$C$9,$A$2:$A$9=A2))),0)

 

@Amazing, That is incredible. Thank you so much Patrick.