Formula is returning double counts when using COUNTIFS

Savita Ubhayakar

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

Savita Ubhayakar

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.

https://community.smartsheet.com/discussion/109788/counting-blank-cells

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

Savita Ubhayakar

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.

https://community.smartsheet.com/discussion/109788/counting-blank-cells

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

Genevieve P.

Hey, thanks so much for posting your solution! It could really help someone else out down the line, we love seeing end results. 🙂