COUNTIFS formula returns zero, confirmed should have positive value

Michele R

Hello - I created this formula and it does not error out but it does not correctly count the field what did I do wrong?

=COUNTIFS({Polaris minus CSC Creator1}, =Creator@row, {Polaris minus CSC Substatus}, "Open-Update Needed" + COUNTIFS({Polaris minus CSC Creator1}, =Creator@row, {Polaris minus CSC Substatus}, "Resolved-Completed"))

Carson Penticuff

Can you explain what you are attempting to do? Here is your formula kind of expanded out:

While this is syntactically correct, it would be impossible to return a result other than zero. Let's walk through what the formula says, and then maybe you can explain how that differs from what you would like it to do.

This will count the number of rows in another sheet that match all of the following conditions:

• {Polaris minus CSC Creator1} is equal to the contents of [Creator] in the current row of the formula's source sheet
• {Polaris minus CSC Substatus} is equal to the text "Open-Update Needed" joined with the "text/number" of the amount of rows in the referenced sheet in which {Polaris minus SCS Creator1} is equal to [Creator] in the current row of the formula's source sheet. This sounds very confusing, but hopefully the screenshot above helps this make sense. Essentially, {Polaris minus CSC Substatus} must contain text such as "Open-Update Needed7"... there will always be a number at the end to match the subcondition. Is this what you intended with your formula?
• {Polaris minus CSC Substatus} must equal "Resolved-Completed"

The reason this formula will always return zero is related to bullet points 2 and 3. They both present a required condition for the contents of {Polaris minus CSC Substatus}. It must equal "Open-Update Needed" + some number AND "Resolved-Completed". It is impossible for it to equal both.

Michele R

Thanks again Carson. I did end up with this formula and it is still not working. It appears the second COUNTIF isn't firing correctly. The first argument does should be zero, the second argument should equal 72. The result is coming at zero.