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
