+ Reply to Thread
Results 1 to 7 of 7

Formula to count blanks and non-blanks with a dynamic range

  1. #1
    Registered User
    Join Date
    07-11-2018
    Location
    Georgia
    MS-Off Ver
    2016
    Posts
    3

    Formula to count blanks and non-blanks with a dynamic range

    I'm trying to solve an issue with counting blanks and/or non blanks in my excel document but knowing I need a variable range.

    Here is the situation.

    I want to count the number of blanks, then the number of non-blanks in Column B for as many rows as I have in Column A with a value. For example, Column A is guaranteed to have a value as it is an ID field and I want to know field completeness for Columns B, C, D, and E but knowing that my number of rows will change as the file is updated over time. So week one I might have 10 rows then week 3 30 rows. Instead of having to make changes to the formula or updating a field that would indicate my number of rows, I just want the formula to know that Column A will have the number rows to count.

    Hope that makes sense.

    Thanks!

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Formula to count blanks and non-blanks with a dynamic range

    Hello and welcome to the forum.

    Attach a sample workbook (not a picture or pasted copy).

    Make sure there is just enough data to demonstrate your need.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  3. #3
    Registered User
    Join Date
    07-11-2018
    Location
    Georgia
    MS-Off Ver
    2016
    Posts
    3

    Re: Formula to count blanks and non-blanks with a dynamic range

    Definitely. Thanks for the welcome.

    File is attached. Again Column A would be the column that defines the number of rows we would count the blanks then non blanks in the other columns. Ideally I would store the counts in a separate worksheet as well.

    Hope that helps!
    Attached Files Attached Files

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Formula to count blanks and non-blanks with a dynamic range

    Try these:

    Blank: =COUNTIFS(A:A,"<>",B:B,"")

    Non-Blank: =COUNTIFS(A:A,"<>",B:B,"<>")-1 or =COUNTA(B:B)-1

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Formula to count blanks and non-blanks with a dynamic range

    If you want those formulas in another worksheet, you can change them to this (just adding in the sheet references):

    =COUNTIFS(Sheet1!A:A,"<>",Sheet1!B:B,"")

    and

    =COUNTIFS(Sheet1!A:A,"<>",Sheet1!B:B,"<>")-1 or =COUNTA(Sheet1!B:B)-1

  6. #6
    Registered User
    Join Date
    07-11-2018
    Location
    Georgia
    MS-Off Ver
    2016
    Posts
    3

    Re: Formula to count blanks and non-blanks with a dynamic range

    This is perfect!!!! It's exactly what I needed but couldn't find!!!!

    Thanks so much!!!!!

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Formula to count blanks and non-blanks with a dynamic range

    You're welcome. Thanks for the rep!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. Removing blanks in a dynamic list range
    By graeme27uk in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 06-28-2018, 12:04 PM
  2. [SOLVED] Not calculating dynamic range with blanks
    By Ochimus in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-23-2018, 05:37 PM
  3. Count Blanks in a dynamic table
    By Alaska1 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-09-2015, 11:36 PM
  4. Remove Blanks With Dynamic Named Range - Error with the formula
    By Tepsjen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-23-2014, 05:34 PM
  5. Dynamic Range Sizes, including blanks
    By Speshul in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-12-2014, 10:52 AM
  6. [SOLVED] Ranking a dynamic range containing blanks
    By freud1 in forum Excel General
    Replies: 7
    Last Post: 10-11-2012, 01:53 PM
  7. Ignoring blanks in a dynamic named range
    By jman0707 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-09-2008, 11:10 AM

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