+ Reply to Thread
Results 1 to 17 of 17

Count blank cells within a range not including fully blank rows

  1. #1
    Forum Contributor
    Join Date
    07-10-2008
    Location
    UK
    Posts
    220

    Count blank cells within a range not including fully blank rows

    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?

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    07-10-2008
    Location
    UK
    Posts
    220

    ???????

    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??

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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.

  5. #5
    Forum Contributor
    Join Date
    07-10-2008
    Location
    UK
    Posts
    220

    Thankyou

    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?

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    I suggest you start a new thread for the new topic.

  7. #7
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127
    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.

  8. #8
    Forum Contributor
    Join Date
    07-10-2008
    Location
    UK
    Posts
    220
    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?

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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.

  10. #10
    Forum Contributor
    Join Date
    07-10-2008
    Location
    UK
    Posts
    220
    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.

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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.

  12. #12
    Forum Contributor
    Join Date
    07-10-2008
    Location
    UK
    Posts
    220
    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?

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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...

  14. #14
    Forum Contributor
    Join Date
    07-10-2008
    Location
    UK
    Posts
    220
    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.
    Attached Files Attached Files

  15. #15
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    07-10-2008
    Location
    UK
    Posts
    220
    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

  17. #17
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Quote 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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1