# Thread: Urgent, can someone help for this formula

1. ## Urgent, can someone help for this formula

I have this file and need to count the continuous date value (=1)
I already made for each month but cannot make for several months.
Now I need to help: how to make formula for B70 have the result as the same B71
Can someone help for this file, thanks you.

Caculation the continuous date value.xlsx

Sorry for my bad explain :-)  Reply With Quote

2. ## Array formula? need to help

Could someone please help to solve this issue (please note that file's format could not be broke for some reason). Thanks  Reply With Quote

3. Array enter this formula into B70, then copy across.
=MAX(MAX(FREQUENCY(IF(B2:B32=1,ROW(B2:B32)),IF(B2:B32=0,ROW(B2:B32)))),MAX(FREQUENCY(IF(B37:B65=1,ROW(B37:B65)),IF(B37:B65=0,ROW(B37:B65)))),MAX(ROW(B2:B32))-MAX((B2:B32=0)*ROW(B2:B32))+MATCH(0,B37:B65,0)-1)

See the attached worksheet.Caculation the continuous date value.xlsx  Reply With Quote

4. ##  Originally Posted by JeffreyWeir Array enter this formula into B70, then copy across.
=MAX(MAX(FREQUENCY(IF(B2:B32=1,ROW(B2:B32)),IF(B2:B32=0,ROW(B2:B32)))),MAX(FREQUENCY(IF(B37:B65=1,ROW(B37:B65)),IF(B37:B65=0,ROW(B37:B65)))),MAX(ROW(B2:B32))-MAX((B2:B32=0)*ROW(B2:B32))+MATCH(0,B37:B65,0)-1)
Thank you for your help, JeffreyWeir. It will help me to save a lot of time on the job.

However, I have tried for some other situations and find several errors may occur (see attached file):
1. If there is not any 0 on the first month, the result will be incorrect
2. If there is not any 0 in the second month, the result is #N/A

Btw, could you tell me how the formula is if there are more than 2 months in the sheet

Again, thank you so much for your kind support  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•