asked on

Count empty cells between rows minus rows that are closed

Haveing trouble resolving the following function in the count colum:
=IF(ISBLANK(G1355),"",ROW()-1-MAX(IF(NOT(ISBLANK($G$1:G1354)),ROW($G$1:G1354))))
it returns this when I use the fill pull down:
Date Event Count
1/1/2009
1/2/2009 yes 1
1/3/2009
1/4/2009 closed 1
1/5/2009
1/6/2009 yes 1
1/7/2009
1/8/2009
1/9/2009 yes 2

I want the function to return the count of blank spaces since the previouse entry but not count the ones that are marked as closed, like the following. I hope this helps get my idea across.

Date Event Count
1/1/2009
1/2/2009 yes 1
1/3/2009
1/4/2009 closed
1/5/2009
1/6/2009 yes 3
1/7/2009
1/8/2009
1/9/2009 yes 2

Thanks experts.

FernandoFernandes

function

=countblank(b1:b400)

FernandoFernandes

sorry.... i hadnt read all the post...

AffinityComp

ASKER

Hello Fernando,

Thanks but only returns the number of blanks in the specified range, if you look at the function in the first example it resets the count after the the peviouse entry however it counts the "closed" entry as an "entry" I would like to skip "closed" or count them as a blank also then reset after the next "yes".

Saqib Husain

Try this formula in row 2 and copy down

=IF(ISBLANK(G2)+(G2="closed"),"",ROW()-1-MAX(IF(NOT(ISBLANK($G$1:G1))*($G$1:G1<>"closed"),ROW($G$1:G1))))

This is an array formula

Saqib

AffinityComp

ASKER

Hello Saqib,

Thanks for the reply it is spot on as for the example I put up, however in my state of trying work out the problem I confused the examle. sorry. it should be like this

Date Event Count
1/1/2009
1/2/2009 yes 1
1/3/2009
1/4/2009 closed
1/5/2009
1/6/2009 yes 2
1/7/2009
1/8/2009
1/9/2009 yes 2

the date that shows closed is NOT counted as on 1/6/2009 showes 2 rather than 3.

My appolagies.... if you like I will award points as you answerd the origninal question.

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

AffinityComp

ASKER

Thank you so much, that works just fine.

Regards,