Forum Discussion
Solved
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 |
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)
5 Replies
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.
- You are welcome!
- Thank you So much Hans 🙂
