Count in orderly manner

[frappe]

Good day,

I have a table that needs to count the sequence.
Problem is formula to get the count in order -> VW, FW, SW, FWW, NTE
If the order is correct should have count as 5

But if when it went back to VW count should be 1.

Every time it goes back to VW it should count as 1.

The count should reflect in the yellow cell

Thank you

 

[myall_blues]

In the third example why isn't the count 5? The order is correct even though the 6th entry is VW.

 

[frappe]

In the third example why isn't the count 5? The order is correct even though the 6th entry is VW.

It went back to VW that's why it should count back to 1

 

[Phuoc]

Try this:

Book1
	A	B	C	D	E	F	G
1
2	5	VW	FW	SW	FWW	NTE
3	1	VW	FW	SW	FWW	VW
4	3	SW	FW	SW	VW	SW	SW
Sheet1

Cell Formulas
Range	Formula
A2:A4	A2	=IFERROR(COUNTA(B2:J2)+1-MATCH(2,1/("VW"=B2:F2),1),0)

 

[Trebor76]

Not sure but maybe this (change the range to suit):

=IF(TEXTJOIN(">",TRUE,B4:G4)="VW>FW>SW>FWW>NTE",5,1)

 

[myall_blues]

Maybe

=IF(COUNTIF(B1:G1,"VW")>1,1,5)

@Trebor76 OP is using 2016 so no TEXTJOIN.

 

[Trebor76]

Thanks Murray.

I did check here before I posted but you obviously can't always rely what the Internet say eh?

I would add another condition because if A to G are all blank your formula will return 5 where the following will return a blank which may suit the OP needs better:

=IF(COUNTBLANK(B4:G4)=6,"",IF(COUNTIF(B4:G4,"VW")>=0,1,5))

 

[myall_blues]

Thanks Murray.

I did check here before I posted but you obviously can't always rely what the Internet say eh?

I was working off Microsoft but you possibly can't rely on them either!

 

[saboh12617]

Hi, for clarification, TEXTJOIN is (sadly) not available on XL2016.

 

[MARK858]

I was working off Microsoft but you possibly can't rely on them either!
View attachment 117290

Microsoft only normally list the function appearing dates for versions they currently support, so if it was in an earlier version they normally wouldn't list it.
It isn't the case with TEXTJOIN though as it first appeared in 2019/365.