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
I think this is happening becuase you are editing a range that exists in the original location. Instead of editing the range in the new location, be sure to delete the entire range and then click in the place where it was in the formula. Then choose another range. <\/p>
I hope that helps.<\/p>
Matt<\/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":107113,"type":"question","name":"How to return a BLANK if we have a BLANK cell using a COUNTIFS formula","excerpt":"This formula is currently working, and it is returning how many items are reviewed for each \"Project Lead\". The first part of the formula is basically saying that we would count them, as long as we have a \"Reviewer\" name added in the \"Reviewer\" column. =IFERROR(COUNTIFS(Reviewer:Reviewer, <>\"//www.santa-greenland.com/community/discussion/comment/\", [Project Lead]:[Project…","snippet":"This formula is currently working, and it is returning how many items are reviewed for each \"Project Lead\". The first part of the formula is basically saying that we would count…","categoryID":322,"dateInserted":"2023-06-29T22:39:31+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T02:51:05+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-30T03:08:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"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-30T03:08:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":24,"score":null,"hot":3376173036,"url":"https:\/\/community.smartsheet.com\/discussion\/107113\/how-to-return-a-blank-if-we-have-a-blank-cell-using-a-countifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107113\/how-to-return-a-blank-if-we-have-a-blank-cell-using-a-countifs-formula","format":"Rich","lastPost":{"discussionID":107113,"commentID":383252,"name":"Re: How to return a BLANK if we have a BLANK cell using a COUNTIFS formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383252#Comment_383252","dateInserted":"2023-06-30T02:51:05+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-30T03:08:32+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-30T02:51:53+00:00","dateAnswered":"2023-06-29T22:55:01+00:00","acceptedAnswers":[{"commentID":383233,"body":"
@Filippo<\/a>, the most inelegant but dead simple thing to do is this:<\/p>
Simplified, it's just this:<\/p>
