IF and COUNT statement Issues

[HAYFISH]

Hello

I have trying to track fault occurrences of certain type of equipment and i need to count the times that a certain device has gone faulty based on the cell value.

If the device goes faulty once a day it is not a problem the issue i have is that when the device goes off twice a day.

I have used the formula =IF(COUNTA(E9:CC9)>0,COUNTA(E9:CC9),"") which works fine if the device goes faulty once a day but if the device goes faulty twice then the above formula only counts the cell value as 1 but i need to count 2 or 3 etc.

For Example
cell e9 i will have 03:34
cell e10 will have 05:28 / 18:33
So the count should be 3 but with the formula above it will only count 2 and i have no idea on best way to count this.

Anyone any ideas on how to resolve this issue; apart from making multiple date entries each with its own time.

 

[wideboydixon]

If you'll always use "/" to show multiple faults, then you could try this:

=IF(COUNTA(E9:CC9)>0,SUM(IF(E9:CC9="",0,LEN(E9:CC9)-LEN(SUBSTITUTE(E9:CC9,"/",""))+1)),"")

Enter it as an array formula using Ctrl+Shift+Enter.

WBD

 

[HAYFISH]

Thanks for the WBD thats exactly what i was looking for