Count blank cells in a row

[crazy-mrt]

Hi all,


I've actually posted this in my introduction section by mistake.. could anyone here help me please?

Am in desperate need to find a formula that counts the number of blanks in a row and then adds up a running total.

To explain the numbers are:

1 1 1 Blank blank blank blank 1111 blank blank blank blank blank blank blank 111 etc....

what I want it to do is count the blanks and if there are more than say 3 blanks say tell me I need to insert X amount of 1's... does that make sense?

Any help would be appreciated.. I've looked at lots of different ways of doing this but cant seem to solve the puzzle.

Cheers for any help already

Regards,

crazy

[mrjinx007]

Can you post a sample of your form?

[Palmetto]

What exactly have you tried?

If we assume you have a header row, we can fix the range for the count and use:

=IF(COUNTBLANK(A2:S2)>3,"Insert " &COUNTBLANK(A2:S2),"")

If you don't have a header row, then you'll need a VBA solution.
Maybe you can make use of / adpat this:

Please Login or Register  to view this content.

[crazy-mrt]

Sample.xlsxSee attached as an example of what it looks like:

See if that helps?

can you define what a header row is please?

[crazy-mrt]

Hi Palmetto,

thanks for your answer and it works.. however what am trying to do, (and maybe I didnt explain properly purely because I may not be understanding it properly), is count the blanks and if there are more then say 3 I want the formula to ignore the first 3 and then tell me how many i have to add.

Or say there is a row of no blanks (say 4 days) then a row of 6 blank days. I want it to say that there needs to be 3 none blanks inserted in this row...

does that make sense?

[jeffreybrown]

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[crazy-mrt]

Apologies was not aware of this rule the x post is here http://www.mrexcel.com/forum/excel-q...-new-post.html

Greatful for any help as am keen to have answer before Monday!

Cheers all

Crazy