Urgent, can someone help for this formula

linh091

New member
Joined
Aug 16, 2013
Messages
3
Reaction score
0
Points
0
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.

View attachment Caculation the continuous date value.xlsx

Sorry for my bad explain :)
 
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
 
Last edited:
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
 

Attachments

  • Caculation the continuous date value_ version2.xlsx
    12.5 KB · Views: 7
Last edited:
Back
Top