I am trying to achieve a formula that will count unique Batch codes in the same date. So for example Column B (Serial no.) should increase a value if above if any of the rows above with the same date has a different batch code. This is part of a larger code
The code I have so far does quite the opposite and I've tried interchanging the criteria etc. but nothing seems to give me the result that I am after.
=IF(COUNTIFS($A$2:A2,A2,$D$2:D2,"<>"&D2)>0,TEXT(COUNTIFS($A$2:A2,A2,$D$2:D2,D2),"00"),TEXT(COUNTIFS($A$2:A2,A2,$D$2:D2,D2),"00"))
Here is a link to an example spreadsheet
I have added a column to show what the expected values should be.
Thank you in advance
CodePudding user response:
What you could try:
Formula in B2
:
=IF(COUNTIF(A$2:A2,A2)>1,IF(COUNTIFS(A$2:A2,A2,D$2:D2,D2)>1,MAXIFS(B$1:B1,A$1:A1,A2,D$1:D1,D2),MAXIFS(B$1:B1,A$1:A1,A2) 1),1)
I'd then use custom number formatting to create the leading zero with: 00
.