=COUNTIFS Function does not appear to work
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?
Best Answer
-
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.
Answers
-
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.
-
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.
-
Excellent. Happy the solution worked for you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!