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

1. ## 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. ## 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.

2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
6) Click on "Close this window"

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!

4. ## 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. ## 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. ## 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. ## Re: Formula to count blanks and non-blanks with a dynamic range

You're welcome. Thanks for the rep!

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

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