try this as a starting point - ranges will need updating

thanks Purfleet,

but here we need to separate counting if the 3 blank cells in a row, so we need to counts, how many times the 3 or more blank rows appear.

secondly, what is the formula used, as this is just a sample data and the actually data is bit bigger then what i have shared, so i need to copy and add them in different sheet,

Regards, Atif

Okay that wasnt clear in the question.

Do you want to count the cells that are blank (so the first row would be would be  3,17) or just the number of gaps there are (2)?

Its not a formula it was done in VBA - you can see the code by pressing alt+ f11

Hello,

added a userform with result by msgbox, and added a command button on the sheet with result in column ("A")

attached test file

Miguel,

thank you Purfleet and Miguel for your reply, yes we need to count consecutive 3 blank rows and consider as "1". now the sheet provide answer 2 for first rows, as it should count 3rows as 1 and if again 3rows and then again 1. but if there are 6 rows together then formula count 2, so each 3 blank rows consider "1".

i hope it is clear.

if any question, please let us know.

Hello,

please clarify what you really want,


in your Post 1, you say

 " here we need to count the consecutive blank cells only, which appear to be continues 3 times or more"

this means cells on the same line, and that's what my macro does for each line! for every 3 consecutive empty cells or more, counts 1

and the Purfleet macro, does very well the empty cell count in the line 

now you say in your Post 6
"yes we need to count consecutive 3 blank rows and consider as "1""

that means counting consecutive empty lines and not cells on one line!

your message in Post 6 contradicts your message in Post 1!



Miguel,

Dear Miguel,

yes, we need to count the consecutive 3 cells in a row to be consider as 1, but when the empty cells reached to 6 (which is dubble of 3), macro count them as 2. same for for 9, if the count reached to 9 consecutive cells, macros them as 3.

What Purfleet macro does that, it still count "1" if the number goes above than 3 consective rows,

Hi Atif,

maybe not the most elegant of solutions, but I've managed to find a solution without VBA

sometimes we use helper columns to achieve our goal, this uses a helper sheet!

as you add extra rows to your Utilisation table, you'll need to extend column A too (this counts your number of distinct 3-blank periods)

if you add extra columns to your Utilisation table, then you'll need to extend the helper sheet too

everything else should take care of itself

anyway, I believe this addresses your original need, do let me know

take care,

jim

Hello,

looking at the next image, the empty cells in each line between the column ("E) and the column (" AD ") were highlighted in red

the sequence of the empty cells is:

row 2 - consecutive empty cells => 3;18
row 3 - consecutive empty cells => 1;2;1;3;1
row 4 - consecutive empty cells => 2;1;5;1;1;1
row 5 - consecutive empty cells => 5;2;6;4
row 6 - consecutive empty cells => 1
row 7 - consecutive empty cells => 26
row 8 - consecutive empty cells => 21;4
row 9 - consecutive empty cells => 1
row 10 - consecutive empty cells => 26
row 11 - consecutive empty cells => 1
row 12 - consecutive empty cells => 26

do you want multiples of 3 empty cells in a row? 

example for row 7, you have 26 consecutive empty cells in a row, (26/3 = 8.6 ...), you want it to appear in this case 8 ?

example for row 5, you have (5;2;6;4) consecutive empty cells, you want it to appear in this case 4 ((5=1 + 2=0 + 6=2 + 4=1) = 4)?

Miguel,

tried to edit my post but Miguel cut me off!

updated post here, and this time WITH an attached file!

Hi Atif,

maybe not the most elegant of solutions, but I've managed to find a solution without VBA

sometimes we use helper columns to achieve our goal, this uses a helper sheet!

as you add extra rows to your Utilisation table, you'll need to extend column A too (this counts your number of distinct 3-blank periods)

if you add extra columns to your Utilisation table, then you'll need to extend row 2 of the helper sheet too

everything else should take care of itself (I tried to get column A and row 2 to spill as well, but couldn't get it to work properly) 

anyway, I believe this addresses your original need, do let me know

take care,

jim

thank Jim,

Yes Miguel, you are right, 26 consecutive empty cells in a row, (26/3 = 8.6 ...)

Regards, Atif

Jim,

i have tried to use your formula "=COUNTIF(Helper!2:2,3)" in original sheet and get error. is there something changed any where?

Regards, Atif

Hi Atif,

did you just try to add that formula to your original workbook?

My solution relies on having the additional helper sheet and various other attributes from the workbook I attached
You may want to add your data to that?

I wish I could find a one-formula solution to your problem, but I'm not sure that's practical

jim

Hi Jim,

sorry, my mistake. i havent looked at the helper sheet.

just looked at the helper sheet and there are lots of formula's. appreciate if you can support me, how to add them in my original data sheet?

i have attached my original data sheet for one row.

Regards, Atif