Formula is returning double counts when using COUNTIFS
Hi Smartsheet Community Forum,
I'm trying to capture the number of studies that are ongoing and I am using a sheet that has a data column "BioA Data Sent". When a study is done, the date is entered into this cell. If a study is ongoing, then that cell is left blank.
I also have another column with a checkbox for when a study is cancelled. If a study is cancelled, then the "BioA Data Sent" cell should be blank.
My thinking is that I can use a COUNTIFS formula that count the number of blank cells in the "BioA Data Sent" column and count the number of cells in which the study is not cancelled (meaning that the checkbox is left empty) I should have the correct number of ongoing studies.
In my sheet, I have a total of 10 studies with "BioA Data Sent" as blank. Of those 10, 7 are cancelled studies. So my end result should be 3.
If I only count the number of blank "BioA Data Sent", the value returns as 20, instead of 10...so it's counting it twice. This is just a quick screenshot...there are 10 rows that fit this situation. I also want to exclude the number of cancelled studies (7).
This is the formula I've tried but it doesn't seem to work at all. I'm getting a value of 13.
Range 4 is the "BioA Data Sent"
Range 1 is "Study Cancelled"
=COUNTIFS({Current Labcorp_GNE Discovery Tracking She Range 4}, ISBLANK(@cell), {Current Labcorp_GNE Discovery Tracking She Range 1}, 0)
Any tips or help would be greatly appreciated! Thank you so much!!
Savita
Best Answer
-
I figured out the issue!! I had blank rows underneath and apparently I also had 10 of them, so it was counting the blank rows without any data.
I found another site on this forum in which I referenced another column.
My new formula is:
=COUNTIFS({Current Labcorp_GNE Discovery Tracking She Range 4}, "", {Current Labcorp_GNE Discovery Tracking She Range 5}, <>"", {Current Labcorp_GNE Discovery Tracking She Range 1}, 0)
Thanks to this amazing forum! So sorry for any hassle or inconvenience in posting this issue!
Savita
Answers
-
I figured out the issue!! I had blank rows underneath and apparently I also had 10 of them, so it was counting the blank rows without any data.
I found another site on this forum in which I referenced another column.
My new formula is:
=COUNTIFS({Current Labcorp_GNE Discovery Tracking She Range 4}, "", {Current Labcorp_GNE Discovery Tracking She Range 5}, <>"", {Current Labcorp_GNE Discovery Tracking She Range 1}, 0)
Thanks to this amazing forum! So sorry for any hassle or inconvenience in posting this issue!
Savita
-
Hey, thanks so much for posting your solution! It could really help someone else out down the line, we love seeing end results. 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!