=COUNTIFS Function does not appear to work

Ray Neave

Hi all,

I am new to Smartsheets and would appreciate a little help.

I have a sheet with a list of UAT defect reports within it and I am using a separate sheet to try to calculate various statistics. When trying to calculate how may defect reports are open for an individual I have used the following formula:

=COUNTIFS({UAT Log [Reported by]:[Reported by]}, "John", {UAT Log [Condition]:[Condition]}, "Open")

From everything I have read this should work however I get an #INVALID REF result.

If I use the "Reference Another Sheet" option to highlight the columns I want in the formula I get the following:

=COUNTIFS({UAT Log Status Reported by}, "John", {UAT Log Status WIP}, "Open")

which gives an answer of Zero despite there being a number of reports still open for John and despite there being no column called "WIP" or "Status WIP" in the source sheet.

Can anyone help please?

Mr. Chris

Hello @Ray Neave

From what I gather from the formulas you provided it appears there's a syntax error on this formula:

=COUNTIFS({UAT Log [Reported by]:[Reported by]}, "John", {UAT Log [Condition]:[Condition]}, "Open")

Anything wrapped in these brackets: { } should be a reference to a column on a different Smartsheet.

Anything wrapped in these brackets: [ ] should be referencing a column on the existing sheet.

Without seeing any screenshots I can only assume that this may be what you're going for:

=COUNTIFS([Reported by]:[Reported by], "John", [Condition]:[Condition], "Open")

I was able to replicate this provided all the columns are in the existing sheet and populated the correct result

If this doesn't work, screen shots clearly indicating the columns you're needing would be helpful.

Mr. Chris

Hello @Ray Neave

From what I gather from the formulas you provided it appears there's a syntax error on this formula:

=COUNTIFS({UAT Log [Reported by]:[Reported by]}, "John", {UAT Log [Condition]:[Condition]}, "Open")

Anything wrapped in these brackets: { } should be a reference to a column on a different Smartsheet.

Anything wrapped in these brackets: [ ] should be referencing a column on the existing sheet.

Without seeing any screenshots I can only assume that this may be what you're going for:

=COUNTIFS([Reported by]:[Reported by], "John", [Condition]:[Condition], "Open")

I was able to replicate this provided all the columns are in the existing sheet and populated the correct result

If this doesn't work, screen shots clearly indicating the columns you're needing would be helpful.

Ray Neave

Hi Chris,

Thank you for your reply.

I have been able to get the formula to work inside the 'UAT Log' sheet following you comments but not on a separate summary sheet when adding the Reference Sheet as per my original post.

I suspect it has something to do with the reason why the "Reference Another Sheet" option was returning spurious column names that did not actually relate the the title I had given the column.

Mr. Chris

https://community.smartsheet.com/discussion/comment/430796#Comment_430796

Excellent. Happy the solution worked for you!