Counting cells with specific data

peter65516

Hi,

I am trying to count the number of cells containing criteria entered using a drop-down box (via a form) in a 'Covid-19 Vaccine Status' column, but I am only wanting the data from rows where another column is blank, that column is called 'Finish Date'. I also want the cells that remain blank in the 'Covid-19 Vaccine Status' and in the 'Finish Date' columns, excluding the blank cells at the bottom of the sheet, where there is no data entered in other cells within those rows.

Kind Regards, Peter

Paul Newcome

For the first one, try this...

=COUNTIFS([Covid-19 Vaccine Status]:[Covid-19 Vaccine Status], Category@row, [Finish Date]:[Finish Date], @cell = "")

For your second question, you have two ways to do this. You can either use the same syntax as above where the criteria for both ranges is @cell = "" and then add in a third range/criteria set for a column that will have data in every single row @ cell <> "".

The easiest way though would be to just use the two range/criteria sets of @cell = "" and then subtract 10 from it. As long as you have 40+ rows, Smartsheet will never generate more than 10 blank rows at the bottom.

=COUNTIFS([Covid-19 Vaccine Status]:[Covid-19 Vaccine Status], @cell = "", [Finish Date]:[Finish Date], @cell = "") - 10

=COUNTIFS([Covid-19 Vaccine Status]:[Covid-19 Vaccine Status], @cell = "", [Finish Date]:[Finish Date], @cell = "", [Other Column]:[Other Column], @cell <> "")

peter65516

Brilliant thank you Paul!

Andrée Starå

Hi @peter65516

I hope you're well and safe!

Not sure I follow but try something like this.

=COUNTIFS([Covid-19 Vaccine Status]@row, <>"", [Finish Date]@row, "")

Did that work/help?

I hope that helps!

Have a fantastic week & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

peter65516

Hi Andree

Sorry my question wasn't very clear. There is really two questions.

I am trying to count the number of cells in the 'Covid-19 Vaccine Status' column, that have a the same words e.g. 'Yes, both done'. But I only want the count to include data from rows where another column 'Finish Date' is blank. The formula below works for counting but it also counts where the 'Finish Date' has a date in it. =COUNTIF([Covid-19 Vaccine Status]:[Covid-19 Vaccine Status], Category@row)

I also want to count the cells that are blank in the 'Covid-19 Vaccine Status' column, where the 'Finish Date' column is blank. With this formula, is there any way that this doesn't count the blank rows at the bottom of the sheet?

Kind Regards, Peter

Paul Newcome

For the first one, try this...

=COUNTIFS([Covid-19 Vaccine Status]:[Covid-19 Vaccine Status], Category@row, [Finish Date]:[Finish Date], @cell = "")

For your second question, you have two ways to do this. You can either use the same syntax as above where the criteria for both ranges is @cell = "" and then add in a third range/criteria set for a column that will have data in every single row @ cell <> "".

The easiest way though would be to just use the two range/criteria sets of @cell = "" and then subtract 10 from it. As long as you have 40+ rows, Smartsheet will never generate more than 10 blank rows at the bottom.

=COUNTIFS([Covid-19 Vaccine Status]:[Covid-19 Vaccine Status], @cell = "", [Finish Date]:[Finish Date], @cell = "") - 10

=COUNTIFS([Covid-19 Vaccine Status]:[Covid-19 Vaccine Status], @cell = "", [Finish Date]:[Finish Date], @cell = "", [Other Column]:[Other Column], @cell <> "")

peter65516

Brilliant thank you Paul!

Paul Newcome

Happy to help. 👍️