COUNTIF one field or another has a value across a row

Options

I need to count how many tests have at least one result without counting all of the results for each row.


For example, in the chart above I have 5 tests. I need to know how many of those have at least one result, but not count the total number of results. In this case I have 4 tests that have at least one result. How can I create a formula to count only one of the three possible results for each test? Thank you for the help - my results keep coming back with errors when I try doing a COUNTIF with an OR statement.

Tags:

Answers

  • Cleversheet
    Cleversheet ✭✭✭✭✭✭
    Options

    @Tshelton , I suggest using the following formula in a helper column you might title something like [Has Result]:

    =COUNT([Result 1]@row:[Result 3]@row)

    You could then use checkbox and/or conditional formatting to highlight the one row in your sample set that has no result; that is, Test Name D has a zero value (0), so that row could be made all yellow with CondFmt, and/or you could use an IF layer like this, if [Has Result] is a checkbox column:

    =IF(COUNT([Result 1]@row:[Result 3]@row) = 0, 0, 1)

    Does that address your question correctly?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!