+ Reply to Thread
Results 1 to 3 of 3

Count Total Blanks of Various Columns but With The Range of Column A to the Last Used Cell

  1. #1
    Registered User
    Join Date
    08-15-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    Count Total Blanks of Various Columns but With The Range of Column A to the Last Used Cell

    Hello,

    I can't wrap my brain around this so I was hoping someone could help me. I need to be able to count all the blanks in columns other than A but only until the last used cell in column A. I am using a formula right now that counts the blanks in column A until the last used cell but I don't know how to apply the range of column A to other columns like B and C. Here is an example of what I hope to accomplish:

    Formula used in A1 that I need applied to other columns but with the range of column A

    ="Total Blanks: "&COUNTIF(INDEX(A2:A8,MATCH(TRUE,A2:A8<>"",0)):INDEX(A2:A8,MATCH(2,1/(A2:A8<>""))),"")

    Here is an example of what B1 and C1, with the formula, would look like if it counted blanks but with the range of column A

    Total Blanks: 3 Total Blanks: 6 Total Blanks: 2
    Data Data
    Data
    Data
    Data Data
    Data Data
    Data
    Data

    If anyone is able to help me with this, it would be very much appreciated. Thank you.

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Count Total Blanks of Various Columns but With The Range of Column A to the Last Used

    Assuming your data starts from A1 in A9 try this
    =COUNTBLANK($A$2:INDEX(A2:A8,,0))

    Hope this help


    If this is helpful click "*" add rep icon in the bottom left corner of my post
    Attached Files Attached Files
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Registered User
    Join Date
    08-15-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Count Total Blanks of Various Columns but With The Range of Column A to the Last Used

    Thanks hemesh. That did work pretty good but the formula adds up all the blanks in Column A and B and C. I need a total per column. Using COUNTBLANK did give me an idea though and I was able to use OFFSET and choose which column to offset to from column A. Then through in the MATCH portion of my previous formula to ensure only blanks are counted up until the last value in column A and it worked pretty good. Thanks again.

    OFFSET to column B from column A range

    =COUNTBLANK(OFFSET(A2,0,1,MATCH(2,1/(A2:A100<>""))))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count number of blanks across several columns
    By shepherdc2814 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-19-2013, 05:26 PM
  2. Count Total of items in a column w/o counting duplicates or blanks
    By Clairebear4 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-23-2013, 10:56 PM
  3. Replies: 3
    Last Post: 05-14-2012, 04:26 PM
  4. How to count x's in column but not blanks
    By Launchnet in forum Excel General
    Replies: 8
    Last Post: 05-27-2010, 12:16 AM
  5. Count the total number of rows in a column, with blanks included
    By jblumGRT in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-22-2009, 06:55 PM

Tags for this Thread

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