Count function
Hi
I need help with a formula. I have a column that I want to count but only based on a values in a different column. I have tried the following formula but it's not working and I can't figure out what I am doing wrong.
=countif([Test Script #]:[Test Script #], [Pass ?]:[Pass ?], <> "N/A")
The Test Script # column is the column I'm trying to count and the Pass ? is the column that has the values of either Yes, No or N/A. If the Pass ? has a value of N/A then I don't want it in the count.
Make sense?
Best Answer
-
ker9 ✭✭✭✭✭✭How about this?
=COUNT([Test Script #]:[Test Script #]) - COUNTIF([Pass?]:[Pass?], ="N/A")
Answers
-
ker9 ✭✭✭✭✭✭
I think you need to remove the comma before the <>
=COUNTIF([Test Script #]:[Test Script #], [Pass ?]:[Pass ?] <> "N/A")
Hope this helps!
-
Carol-Anne Cerbone ✭✭✭✭✭
Thanks, that removed the error message but it is still counting the N/A in the total count. Any other suggestions? Below is the updated formula
=COUNTIF([Test Script #]:[Test Script #], [Pass?]:[Pass?] <> "N/A")
I also tried
=COUNTIFS([Test Script #]:[Test Script #], [Pass?]:[Pass?] <> "N/A")
-
ker9 ✭✭✭✭✭✭
Try this instead:
=COUNTIFS([Test Script #]:[Test Script #], <>"", [Pass?]:[Pass?], <>"N/A")
This may not count properly if the Pass? column is blank - do we need to consider that?
Can you count just the items in the Pass column?
-
Carol-Anne Cerbone ✭✭✭✭✭
@ker9- that didn't work either. Any other suggestions?
-
ker9 ✭✭✭✭✭✭
Can you provide detail of what type of data is in your columns or a screen shot?
Is there data in the [Pass?] column for each item in the [Test Script #] column?
-
Carol-Anne Cerbone ✭✭✭✭✭
The column type for Test Script # is a auto generated number system and the Pass column is a drop down list
-
ker9 ✭✭✭✭✭✭
How about this?
=COUNT([Test Script #]:[Test Script #]) - COUNTIF([Pass?]:[Pass?], ="N/A")
-
Carol-Anne Cerbone ✭✭✭✭✭
That worked!!!! Thank you so much
Help Article Resources
Categories
=[Target End Date]1<\/p>
with this:<\/p>
=Index([Target End Date]:[Target End Date], 1)<\/p>
the second value in an index function is the row—usually we make this a MATCH lookup function but it can also be a number.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107112,"type":"question","name":"How to we exclude a specific name from a DISTINCT LIST USER formula","excerpt":"I would like to exclude a specific user name from this formula. The formula is working fine, and it is returning a distinct list of user names. However, we need to exclude certain users, that are no longer in the group. For example, how do I say to exclude user \"Joe Black\" from this list? =IFERROR(INDEX(DISTINCT({Trial…","snippet":"I would like to exclude a specific user name from this formula. The formula is working fine, and it is returning a distinct list of user names. However, we need to exclude certain…","categoryID":322,"dateInserted":"2023-06-29T22:30:22+00:00","dateUpdated":"2023-06-29T22:33:31+00:00","dateLastComment":"2023-06-30T13:51:12+00:00","insertUserID":157974,"insertUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T14:54:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":157974,"lastUserID":157974,"lastUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T14:54:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":66,"score":null,"hot":3376213894,"url":"https:\/\/community.smartsheet.com\/discussion\/107112\/how-to-we-exclude-a-specific-name-from-a-distinct-list-user-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107112\/how-to-we-exclude-a-specific-name-from-a-distinct-list-user-formula","format":"Rich","lastPost":{"discussionID":107112,"commentID":383323,"name":"Re: How to we exclude a specific name from a DISTINCT LIST USER formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383323#Comment_383323","dateInserted":"2023-06-30T13:51:12+00:00","insertUserID":157974,"insertUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T14:54:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T13:43:49+00:00","dateAnswered":"2023-06-30T12:01:20+00:00","acceptedAnswers":[{"commentID":383296,"body":"
You can use a COLLECT Function<\/a> to filter results, for example:<\/p>
