Calculating no of events

[lokuwel]

PaI am a freelancer.My client ask me to create following function in his excel sheet.Excel sheet has a column called "Uptime" It contains 0,1 . If the cell values is equal to 0 then it is an Event otherwise it is not.

Following description is the original requirement made by my client.

About "No of Event" - The number of times the "uptime' = 0 parameter (This counter only moves up when once after 'Uptime=1'. ). So if you look at the "uptime" column you will see it almost always = "1" but sometimes it = "0". When this parameter 'uptime'=0 it means it is an event. BUT when 'uptime' = 0 and in time it has another one it means it is the same event. So if we have for example in the column uptime= 1,1,1,1,1,0,0,0,0,0,1,1,1,1,1 this is only 1 event. This is for example 1,1,1,1,1,0,0,0,0,0,1,1,1,1,0,0,00,1,1,1 has 2 events.

Any help? Please find the attached sample excel sheet.It clearly shows the requirement.
Thank you
Franz

 

[NARAYANK991]

Hi ,

Try this :

=SUMPRODUCT(($A$2:$A$20=1) * ($A$3:$A$21=0))

Narayan

 

[lokuwel]

Hi ,

Try this :

=SUMPRODUCT(($A$2:$A$20=1) * ($A$3:$A$21=0))

Narayan

Thank you very much sir! It works for me!!!

 

[NARAYANK991]

Hi ,

Correct that to this :

=IF(A2 = 0, 1, 0) + SUMPRODUCT(($A$2:$A$20=1) * ($A$3:$A$21=0))

This is to take care if the range starts with a 0.

Narayan

 

[lokuwel]

Hi ,

Correct that to this :

=IF(A2 = 0, 1, 0) + SUMPRODUCT(($A$2:$A$20=1) * ($A$3:$A$21=0))

This is to take care if the range starts with a 0.

Narayan

 

[lokuwel]

Thank you!