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

Matt Suderman

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?

Jeff Reisman

@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.

Kelly P.

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

@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

@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!