COUNTIF formula returning 10 extra blank lines

I wrote a formula to count the total number of unassigned purchase orders in my sheet, which looked to be working at first glance, but upon comparing it to the actual data in the sheet, realized that it is counting 10 ghost orders.

This is my formula:

=COUNTIF({Assigned}, ISBLANK(@cell))

The Assigned column is a contact list drop-down that employees use to assign themselves orders. I can't figure out why it would count lines that don't exist in the sheet and any help would be greatly appreciated!

Find more posts tagged with

Workspaces

Sheets

Formulas

Solution Center

Accepted answers

Galadriel975

I solved this issue with the use of a helper column, so my new formula looks like this:

=COUNTIF({Help}, @cell = [Primary Column]$29)

I wanted to leave the post up to help anyone else having the same issue.

My helper column has a formula in it that returns values to rows based on data that then gets used to "count" the numbers of rows with the appropriate value in the help column.

The helper formula is a bit long at this point, but it does work. This is what it looks for:

=IF([Material Order Status]@row = "Requested", "Missing Materials", IF([Material Order Status]@row = "Ordered", "Missing Materials", IF([PoD uploaded to SAP]@row = 1, "Complete", IF([Tracking Uploaded to SAP]@row = 1, "Awaiting Delivery", IF([TO's/Label Requests Uploaded to SAP]@row = 1, "Awaiting Tracking", IF([Date Uploaded to MIMs]@row = "", "In Progress", IF([Assigned To]@row = "", "Unassigned")))))))

Basically I have the COUNTIF formula count the values from this cell which makes everything a lot easier.

Paul Newcome

There are always (at least) 10 empty rows at the bottom of every sheet. Your ISBLANK function is picking up on that.

You can do one of two things. You can either just subtract 10

=COUNTIF({Assigned}, ISBLANK(@cell)) - 10

or you can include a second range/criteria set to cover a column that will always have data in it and set the criteria to be "is not blank"

=COUNTIFS({Assigned}, ISBLANK(@cell), {Task Name}, @cell <> "")

All comments

Galadriel975

I solved this issue with the use of a helper column, so my new formula looks like this:

=COUNTIF({Help}, @cell = [Primary Column]$29)

I wanted to leave the post up to help anyone else having the same issue.

My helper column has a formula in it that returns values to rows based on data that then gets used to "count" the numbers of rows with the appropriate value in the help column.

The helper formula is a bit long at this point, but it does work. This is what it looks for:

Basically I have the COUNTIF formula count the values from this cell which makes everything a lot easier.

Paul Newcome

There are always (at least) 10 empty rows at the bottom of every sheet. Your ISBLANK function is picking up on that.

You can do one of two things. You can either just subtract 10

=COUNTIF({Assigned}, ISBLANK(@cell)) - 10

or you can include a second range/criteria set to cover a column that will always have data in it and set the criteria to be "is not blank"

=COUNTIFS({Assigned}, ISBLANK(@cell), {Task Name}, @cell <> "")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.