Count blank cells within a range not including fully blank rows

**twofootgiant** · 07-15-2008, 07:53 AM

Hi

Ok, I can count the blank cells withiin a range using

=COUNTBLANK(C6:AD2506)

But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.

It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.

Any help?

**NBVC** · 07-15-2008, 08:08 AM

I would add a helper column that counts blanks in each row...

eg. =Countblank(C6:AD6) copied down...

then use formula: =SUM(AE6:AE2506)-SUMIF(AE6:AE2506,COLUMNS(C6:AD6))

where AE6:AE2506 is your helper range....

The column can be hidden if desired.

**twofootgiant** · 07-15-2008, 08:28 AM

ok i have put the helper column on another sheet....its worked fine.

BUT..i dont understand what the other formula is doing, I have use:

=SUM(AM6:AM2506)-SUMIF(AM6:AM2506,COLUMNS('enter data'!C6:'enter data'!AD6))

and its comming up with what seems to be a completley unrelated number.

could you please explain that formula abit better?

thanks??

**NBVC** · 07-15-2008, 08:48 AM

The SUM() part is summing all the results of the Countblanks() in your helper column which gives a total of blanks in your entire range.

Then we have to subtract the number of Countblanks() that occur in totally empty rows... The Sumif() does that...

So the Sumif() part says to sum the values in your helper column that equals the same number of columns in the range (which would mean all columns are blank).

When you subtract this result from the Sum() result, you should get the number of blanks excluding blanks found in totally blank rows...

This part of your formula: COLUMNS('enter data'!C6:'enter data'!AD6)

should just contain the entire range of columns your are addressing... I.e. use the same range you used for the Countblank() formula in your helper column.

**twofootgiant** · 07-15-2008, 09:10 AM

Thanks that works perfectly.

On to the next level:

I need to display that value in a text box on a user form.

This user form will have a button on it which takes the user to the 1st empty cell within that range (again not including the completley empty rows)

any idea how to do these?

**NBVC** · 07-15-2008, 09:23 AM

I suggest you start a new thread for the new topic.

**BigBas** · 07-15-2008, 09:35 AM

You can also use a user defined function to count the blank cells in non blank rows:

Please Login or Register  to view this content.

Anywhere on your worksheet, you can enter

Please Login or Register  to view this content.

Let me know if that helps.

**twofootgiant** · 07-15-2008, 10:19 AM

thanks for the replys. lets say I only needed to do the same formula but this time not including columns AC,AB,Y,X,U,V,W,T,S,P,E,D.

What would i have to change:

=SUM(AM6:AM2506)-SUMIF(AM6:AM2506,COLUMNS('enter data'!C6:'enter data'!AD6))

to?

**NBVC** · 07-15-2008, 10:38 AM

Try changing the Countblank() formula in the helper column (AM) to:

=SUM(COUNTBLANK(C6),COUNTBLANK(F6:O6),COUNTBLANK(Q6:R6),COUNTBLANK(Z6:AA6),COUNTBLANK(AD6))

copied down

then the final formula would be:

=SUM(AM6:AM2506)-SUMIF(AM6:AM2506,COLUMNS(C6:AD6)-12)

adjusting ranges again to suit.

**twofootgiant** · 07-15-2008, 11:25 AM

Hi

This works very well, thankyou

Unfortunatley this formula does not miss out the fully blank rows within the data range. I do not want it to count rows that have no data between cells C:AD.

**NBVC** · 07-15-2008, 11:29 AM

I assumed you only want to look at certain columns and see if the cells are blank or not in those picked columns....

I don't really know what you want anymore... so the best thing is to post a sample worksheet and specify expected results and why.

**twofootgiant** · 07-15-2008, 11:42 AM

I have a sheet with 28 columns to be filled in by the user. 14 of these columns are mandatory so the formula is to count how many mandatory fields they have not filled in.

There are 2500 rows which can be filled in so I only want to count the blank mandatory fields in the rows which have been party or fully filled in.
Otherwise if the user only filled in 100 rows, when i used the function it would bring up 2400 rows worth of blank cells.

So if there is no data on a row (within the C6:AD2506 range) then the blank mandatory fields within that row should not be added to the count.

This function will be used to check errors on the data that they have filled in, not nessasserily the entire range.

Does that make it any clearer?

**NBVC** · 07-15-2008, 12:10 PM

so if all those mandatory columns are incomplete, but other cells in the row are complete, how should that be treated in the counting of blanks?

again, an attachment would be much better for visualization...

**twofootgiant** · 07-16-2008, 03:39 AM

If any data at all has been entered by the user into the row then the blank mandatory fields should be included in the count.
The user will enter thier data horizontally, one row = one product. So if there is no data at all in ANY of the fields on a row then the user obviously has not got that far down the sheet yet, therefore the blank madatory fields within the blank row should NOT be included in the count.

Heres the attachment..

The data is entered from C6:AD2506, the red colums are mandatory and the yellow are not. The rows are labeled 'Item1' 'Item2' etc, each Item = one product. One product per row.

**NBVC** · 07-16-2008, 08:36 AM

Ok... here we go...

See attached.

Two helper columns inserted in columns AM:AN

One counts number of blank "mandatory" fields, the other counts number of blank mandatory/non-mandatory fields in the row..

In AM6:

Please Login or Register  to view this content.

in AN6:

Please Login or Register  to view this content.

both formulas copied all the way down.

In AO6, the result formula:

Please Login or Register  to view this content.

which sums the number of mandatory fields if any of the mandatory/non-mandatory fields are filled.

I hope this does the trick.

**twofootgiant** · 07-16-2008, 09:32 AM

thankyou so much for that, it works perfectly.

any chance you could talke a look at my newest post...its kind of the follow on from this one?

thanks again

**NBVC** · 07-16-2008, 09:43 AM

Originally Posted by twofootgiant

thankyou so much for that, it works perfectly.

any chance you could talke a look at my newest post...its kind of the follow on from this one?

thanks again

You should at least provide me the link...

If, its the userform question... then sorry, not my expertise.