Using Column Formulas for CountIfs / SumIfs evaluation aren't working

Options

I have a sheet for Test Scripts. In this sheet we have an AutoNumber column so we have a fixed ID. We also have a calculated column with the ID and the Name.

ID | Name | IDName

1 | This Script | 1.This Script

2 | That Script | 2.That Script

I also have a Test Result Sheet. in this sheet we have a Dropdown with all the IDNames and a result column. We added a calculated column to pull out the test script ID.

Result Name | Result | ScriptID

1.This Script | Fail | 1

2.That Script | Fail | 2

2.That Script | Pass | 2

Then...back on the Test Scripts sheet, we have a Calculated Column that tries to pull the the Count by Status.

=COUNTIFS({Test Results ScriptID}, [ID]@row, {Test Results Result}, "Pass")

I'm getting zeros for all values.

I've tried to hard code the ID number in the formula and I still get zeros. So, I feel like it can't lookup into a Calculated column.


Anyone?

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @Matt Suderman

    The formula looks correct, so this might be some kind of data format issue.

    The first thing I would do to troubleshoot is doublecheck your ranges. Make sure they are pointing to the right sheet and that you've selected the entire column.

    Next, try adding an empty text/number column to the results sheet, and use it to verify that the values are what you think they are. Try something simple like:

    =IF(Result@row = "Pass", "Yes", "No")

    That will tell you right away if the COUNTIFS just can't find "Pass", because maybe there's an extra space in the value or something.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Kelly P.
    Kelly P. ✭✭✭✭✭✭
    Options

    Interesting. I got the same result as you. However, using Result Name and IDName (instead of ScriptID and ID) provides the correct results.

    =COUNTIFS({TestScripts_ScriptID}, IDName@row, {TestScripts_Results}, "Pass")

  • Matt Suderman
    Matt Suderman ✭✭
    edited 02/15/23
    Options

    @Jeff Reisman ,

    AAAAHHHHHHH!!!!!!!

    You were totally right. It was the TYPE of value my formulas where resolving to.

    On the Results sheet, the Test Number was evaluating as text. In the Tests sheet, the autonumber is a number.

    I changed the Test Number to Value (asdfasdfasdf) and it worked.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @Matt Suderman I think what you have going on is a mismatch in data types. Your Autonumber ID column in the test scripts sheet is a number value, but your calculated ScriptID in the results sheet is number stored as text. Those will never match unless you convert the ScriptID value to be stored as number, which fortunately is easy!

    Just wrap your whole formula in ScriptID in VALUE:

    =VALUE(put your formula here)

    That's all you have to do!

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!