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!

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"

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!

Try these:

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

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

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

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

Thanks so much!!!!!

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.