Formula Help: Counting Blanks

[PixieCat]

Hi

I am struggling with writing a formula to achieve the following:

We want to count how many times there are two or more consecutive blanks.

Below is a snapshot of what the data looks like.

https://imgur.com/a/SP7Ojyz

In addition, while we are here -

We have the following formula:

=(IF((INDEX($K$3:$CD$3,MATCH(CR3,$K$2:$CD$2,0)))="","",MATCH(TRUE,(OFFSET((INDEX($K$3:$CD$3,MATCH(CR3,$K$2:$CD$2,0))),0,1)):CD3<>"",0)))-1

This is counting how many "blanks" are following a "Y" on a set date, this works for the first 10 rows but for some reason it stops working and returns N/A for no reason?

Thanks in advance

Cat

 

[jorismoerings]

Hi,

Try this:

=SUMPRODUCT(--(FREQUENCY(IF(NOT(ISBLANK(A$2:CD$2)),"",COLUMN(A$2:CD$2)),IF(ISBLANK(A$2:CD$2),"",COLUMN(A$2:CD$2)))>=2))

enter it as an array formula by using <CTRL-Shft-Enter> to onfirm.