SOLVED

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

Occasional Contributor

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

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) 121637411 13192 RT-KSA ONLINE active 1 1 1 121637411 13192 RT-KSA ONLINE active 2 0 0 121637411 13800 RT KSA - Ecom active 1 1 2 121637411 13800 RT KSA - Ecom active 2 0 0 121637411 13800 RT KSA - Ecom active 3 0 0 121637411 13800 RT KSA - Ecom active 4 0 0 121637411 13192 RT-KSA ONLINE active 3 0 0 121637411 13191 KSA POS active 1 1 3
5 Replies

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

In G2:

=IF(F2,SUM(F\$2:F2),0)

Fill down.

best response confirmed by sherikhan88 (Occasional Contributor)
Solution

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

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)``

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

Thank you So much Hans

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

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

You are welcome!