Count If

[mouzzampk2014]

Hi all, need little bit help with the calculation. I have spreadsheet which shows if camera is working or not.

I am trying to count number of days camera wasn't working. In Status column I have Working, Not Working and Out of Schedule.

I cannot figure it out how to count if camera wasn't working before out of schedule and/or after out of schedule. Basically I am trying to see if camera was not working before out of schedule that means camera was also not working on out of schedule days.

Camera	Date	Status
MT2889	01/01/2023​	Working
MT2889	02/01/2023​	Working
MT2889	03/01/2023​	Not Working
MT2889	04/01/2023​	Not Working
MT2889	05/01/2023​	Out of Schedule
MT2889	06/01/2023​	Out of Schedule
MT2889	07/01/2023​	Not Working
MT2889	08/01/2023​	Working
MT2889	09/01/2023​	Working
MT2889	10/01/2023​	Not Working
MT2889	11/01/2023​	Not Working
MT2889	12/01/2023​	Out of Schedule
MT2889	13/01/2023​	Out of Schedule
MT2889	14/01/2023​	Working

 

[braindiesel]

With your table and CAMERA in cell A1,
=IF(OR(C2="Working",AND(C2="Out of Schedule",D1=0)),0,1)
This gives a 0 if it is adjacent to a Working or if on an OUT day, it was working the day before
Otherwise it is a 1
Then you can do a simple SUMIF and even incorporate a date range if you wish

There are likely more sophisticated ways but this seems to work

 

[mouzzampk2014]

With your table and CAMERA in cell A1,
=IF(OR(C2="Working",AND(C2="Out of Schedule",D1=0)),0,1)
This gives a 0 if it is adjacent to a Working or if on an OUT day, it was working the day before
Otherwise it is a 1
Then you can do a simple SUMIF and even incorporate a date range if you wish

There are likely more sophisticated ways but this seems to work

This is amazing and worked thank you so much.

Can I just ask what is this doing

D1=0

 

[braindiesel]

This is amazing and worked thank you so much.

Can I just ask what is this doing

D1=0

The D1 = 0 is checking if the previous value was a 0 (working) so working on one day and Off the next means working on the Off day
If it was not a 0... a 1, that means that it was NOT working on the day preceding the OFF day, so not working on the OFF day

 

[mouzzampk2014]

The D1 = 0 is checking if the previous value was a 0 (working) so working on one day and Off the next means working on the Off day
If it was not a 0... a 1, that means that it was NOT working on the day preceding the OFF day, so not working on the OFF day

Thank you for your time. Much appreciated.

 

[mouzzampk2014]

Hi, do you know how I can use this in power query?

Thanks

 

[braindiesel]

Hi, do you know how I can use this in power query?

Thanks

Power Query is on my learn list for the month so I cannot help you there