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!
Best Answers
-
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.
-
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 <> "")
Answers
-
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.
-
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 <> "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!