Need Help - Count the # of times a change (data) occurs in a range of cells

[jgtubbs]

Hello MrExcel Members,

I have an issue that is beyond my scope of excel skills. I need to determine how many times , the equipment "fails" over a specific date range. Basically, when the "status" goes from "In-Service" to "OOS" only. For example: Row 2, I have highlighted "when a failure" occurs and no matter how long the failure lasts, only the initial "failure" counts as "1". A formula to count such over a large range of columns and rows, would be most helpful. Thank you for any assistance.

 

[DRSteele]

Welcome to the Forum.

I think this works. There are probably better ways, but try this out.

MrExcel posts20.xlsx
	D	E	F	G	H	I	J	K	L	M	N
2										Changes from	In Service
3										to	OSS
4
5
6	1/23/2023	1/24/2023	1/25/2023	1/26/2023	1/27/2023	1/28/2023	1/29/2023	1/30/2023	1/31/2023	2/1/2023	Failures
7	In Service	OSS	OSS	In Service	In Service	In Service	OSS	In Service	In Service	In Service	2
Sheet14

Cell Formulas
Range	Formula
N7	N7	=SUM(--(INDEX(D7:M7&TRANSPOSE(D7:M7),SEQUENCE(COUNTA(D7:M7)-1)+1,SEQUENCE(COUNTA(D7:M7)-1))=N2&N3))

 

[Fluff]

Hi & welcome to MrExcel.
Another option

=COUNTIFS(C2:K2,"in service",D2:L2,"OSS")

 

[jgtubbs]

Welcome to the Forum.

I think this works. There are probably better ways, but try this out.

MrExcel posts20.xlsx
	D	E	F	G	H	I	J	K	L	M	N
2										Changes from	In Service
3										to	OSS
4
5
6	1/23/2023	1/24/2023	1/25/2023	1/26/2023	1/27/2023	1/28/2023	1/29/2023	1/30/2023	1/31/2023	2/1/2023	Failures
7	In Service	OSS	OSS	In Service	In Service	In Service	OSS	In Service	In Service	In Service	2
Sheet14

Cell Formulas
Range	Formula
N7	N7	=SUM(--(INDEX(D7:M7&TRANSPOSE(D7:M7),SEQUENCE(COUNTA(D7:M7)-1)+1,SEQUENCE(COUNTA(D7:M7)-1))=N2&N3))

Thank you so very much!! This was perfect!!! Much Appreciated!

 

[DRSteele]

Thank you so very much!! This was perfect!!! Much Appreciated!

You're welcome. But I think Fluff's simple answer is far superior. I don't know why it works, but it sure is better.