Finding consecutive same numbers in rows

[Mister Bear]

I am working on a 44 columns (P4:BG4) on a worksheet for monitoring daily sales of sales agents. It is company policy to call the attention of an agent once he has zero "0" sales for 2 consecutive days. On the next column (BH4) I would like to put in a formula that can trace two consecutive zeros. To count the days with no sales, I used:
=countif(p4:bg4,0)
which worked fine to give me the total number of no sales days. But now I have been asked to count the number of CONSECUTIVE DAYS with no sales. Can you please help me?

 

[Naresh Yadav]

Hi,

can we have the example with your desire result ?

 

[xlstime]

Hey Mister bear,

please refer below thread

http://chandoo.org/forum/threads/determine-number-of-occurrences-in-trend.30603/

I am working on a 44 columns (P4:BG4) on a worksheet for monitoring daily sales of sales agents. It is company policy to call the attention of an agent once he has zero "0" sales for 2 consecutive days. On the next column (BH4) I would like to put in a formula that can trace two consecutive zeros. To count the days with no sales, I used:
=countif(p4:bg4,0)
which worked fine to give me the total number of no sales days. But now I have been asked to count the number of CONSECUTIVE DAYS with no sales. Can you please help me?

 

[Chihiro]

You'd need additional condition to formula used in the link provided by xlstime, if your range contain blanks.
=MAX(FREQUENCY(IF(($P4:$BG4=0)*($P4:$BG4<>""),COLUMN($P4:$BG4)),IF($P4:$BG4<>0,COLUMN($P4:$BG4))))

Also note that this will provide maximum consecutive days with 0 sales and will not count all consecutive days with 0 sales.

To count # of occurrences of consecutive 0 sales you can do.
=SUM(IF(FREQUENCY(IF(($P4:$BG4=0)*($P4:$BG4<>""),COLUMN($P4:$BG4)),IF(($P4:$BG4<>0),COLUMN($P4:$BG4))),1,0))

Both formula confirmed as Array (CSE).