3 Replies Latest reply on May 8, 2018 9:40 AM by Ashan Bogollagama

# How to count consecutive blanks in a row

Hi,

I have a workbook (below) that counts a members attendance to an annual meeting. If attended there is a designated 1 for that year. Is it possible to count the number of consecutive blanks or gaps between attendance?

I would like to determine if there are any trends in gap years.

Thanks,

Ashan

• ###### 1. Re: How to count consecutive blanks in a row

Hi Ashan,

Does the attached and below work for you?

1 of 1 people found this helpful
• ###### 2. Re: How to count consecutive blanks in a row

Pl check the screenshot and attached.

If it Helps, Pl mark it Helpful and CORRECT to close Thread.

Thanks

Deepak

• ###### 3. Re: How to count consecutive blanks in a row

Hi Don,

This helped a lot, Thanks !

I modified it slightly to see if there is a "once every 3 years" trend in a 6 year period

IF ISNULL([SUM 2013]) AND ([SUM 2014])=1 AND ISNULL([SUM 2015]) AND ISNULL([SUM 2016]) AND ([SUM 2017])=1 THEN "True"

ELSEIF ISNULL([SUM 2013]) AND ISNULL([SUM 2014]) AND ([SUM 2015])=1 AND ISNULL([SUM 2016]) AND ISNULL([SUM 2017]) THEN "True"

ELSEIF ([SUM 2013])=1 AND ISNULL([SUM 2014]) AND ISNULL([SUM 2015]) AND ([SUM 2016])=1 AND ISNULL(2017) THEN "True"

END