I need to build a formula that looks at some cells in a column but not all of them.

Rob Peek · 01/26/16

Hi everyone,

I'm trying build a formula that looks at a column of check boxes, and if certain boxes are checked off, it will give me a "green light".

Here is the formula that currently works:

=IF(COUNTIF(Completed13:Completed26, 0) > 0.5, "Red", "Green")

It compiles all the check boxes in my column from row 13 to 26, however I need it to omit at least one cell, potentially more.

Any help or suggestions are greatly appreciated!

JamesR · 01/27/16

Rob,

It depends on what is in the cells you want to omit. You may be able to use an ANS function in the condition portion of the IF Function.

Need more info to take it further.

Rob Peek · 01/27/16

It's a simple checklist that I modified from a template. The cells in the "completed" column are either checked or not checked. The function should look at the cells and see if the required portions are checked off.

Here's a screenshot of how it's currently set up. The highlighted cells are what I'm having problems with. I can set it up to look at the entire column and it works just fine, but if I want to omit 1 or 2 checkboxes, I am running into trouble.

Does this help?

Screenshot 2016-01-27 09.21.54.png

JamesR · 01/27/16

Try countifs().

There needs to be a value somewher that uniquly identifys the row(s) you do not want included ub the count.

The first two arguments will count the number of cells that meet your criteria (0) but only those that also meet the other arguments (<> "Do not count") for example.

COUNTIFS(): Applies criteria to cells across multiple ranges and counts the number of times all criteria are met

Example: =COUNTIFS(Done:Done, 1, [Task Name]:[Task Name], "Task A")
Result: 1

Welcome to the Smartsheet Forum Archives

I need to build a formula that looks at some cells in a column but not all of them.

Comments

Categories