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
<\/p>
This does the percentage:<\/p>
(TODAY() - Start@row) \/ (Finish@row - Start@row)<\/p>
<\/p>
This caps it at 100%:<\/p>
MIN(..............., 1)<\/p>
<\/p>
This keeps it from going negative for future tasks:<\/p>
MAX(..............., 0)<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106871,"type":"question","name":"Parent Status based on Children Harvy ball status.","excerpt":"Dear team, I am trying to build a status update tracker, for teams to update each milestone. They are expected to update each milestone using the harvey ball status (0% \/25% \/50 % \/75% \/100%). I am trying to automate the status of parent based on children's Harvey ball status as below with logic as \" If the status of all…","categoryID":322,"dateInserted":"2023-06-25T03:09:21+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T02:39:36+00:00","insertUserID":162738,"insertUser":{"userID":162738,"name":"ChaitanyaK","url":"https:\/\/community.smartsheet.com\/profile\/ChaitanyaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7z8le8P6Q_o!5NnvuQ5DbTI!qYVEcd1EfTU","dateLastActive":"2023-06-26T04:03:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162738,"lastUser":{"userID":162738,"name":"ChaitanyaK","url":"https:\/\/community.smartsheet.com\/profile\/ChaitanyaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7z8le8P6Q_o!5NnvuQ5DbTI!qYVEcd1EfTU","dateLastActive":"2023-06-26T04:03:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3375410937,"url":"https:\/\/community.smartsheet.com\/discussion\/106871\/parent-status-based-on-children-harvy-ball-status","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106871\/parent-status-based-on-children-harvy-ball-status","format":"Rich","lastPost":{"discussionID":106871,"commentID":382196,"name":"Re: Parent Status based on Children Harvy ball status.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382196#Comment_382196","dateInserted":"2023-06-26T02:39:36+00:00","insertUserID":162738,"insertUser":{"userID":162738,"name":"ChaitanyaK","url":"https:\/\/community.smartsheet.com\/profile\/ChaitanyaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7z8le8P6Q_o!5NnvuQ5DbTI!qYVEcd1EfTU","dateLastActive":"2023-06-26T04:03:19+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/81O3LR1GWG7D\/picture1.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Picture1.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T02:28:56+00:00","dateAnswered":"2023-06-25T04:29:41+00:00","acceptedAnswers":[{"commentID":382168,"body":"
With the symbols you are using, the options would typically be represented by the text entries [Empty, Quarter, Half, Three Quarter, Full]. In this case, this should work:<\/p>
=IF(COUNTIF(CHILDREN([BallCell]@row), \"Empty\") = COUNT(CHILDREN([BallCell]@row)), \"Not Started\", IF(COUNTIF(CHILDREN([BallCell]@row), \"Full\") = COUNT(CHILDREN([BallCell]@row)), \"Completed\", \"In Progress\"))<\/p>
If you do have your sheet setup somehow so that the entries are respresented by [0, 0.25, 0.50, 0.75, 1], then this should work:<\/p>
=IF(COUNTIF(CHILDREN([BallCell]@row), < 0.25) = COUNT(CHILDREN([BallCell]@row)), \"Not Started\", IF(COUNTIF(CHILDREN([BallCell]@row), 1) = COUNT(CHILDREN([BallCell]@row)), \"Completed\", \"In Progress\"))<\/p>
In either case, you will need to substitute [BallCell] with the name of the column with your symbols. The column name is not visible in your screenshot.<\/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":106869,"type":"question","name":"Formula to Assign Symbol based on a cell's value","excerpt":"This is a complicated one that I once got to work with RGY balls, but cannot figure out how to apply this to the Pain scale emojis. What I am trying to do: (Using the pain scale symbols) Have symbols automatically changed based on the value in the corresponding cell. I am already using a formula to assign a value based on…","categoryID":322,"dateInserted":"2023-06-24T20:02:20+00:00","dateUpdated":null,"dateLastComment":"2023-06-25T22:29:54+00:00","insertUserID":158092,"insertUser":{"userID":158092,"name":"MeganJF","url":"https:\/\/community.smartsheet.com\/profile\/MeganJF","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DgFow9e-1vo!YfMmmJOUTjo!UDpVBcB6ikY","dateLastActive":"2023-06-25T17:43:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":139601,"lastUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-26T04:50:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":31,"score":null,"hot":3375372134,"url":"https:\/\/community.smartsheet.com\/discussion\/106869\/formula-to-assign-symbol-based-on-a-cells-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106869\/formula-to-assign-symbol-based-on-a-cells-value","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106869,"commentID":382193,"name":"Re: Formula to Assign Symbol based on a cell's value","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382193#Comment_382193","dateInserted":"2023-06-25T22:29:54+00:00","insertUserID":139601,"insertUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-26T04:50: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-25T17:10:13+00:00","dateAnswered":"2023-06-25T03:32:30+00:00","acceptedAnswers":[{"commentID":382165,"body":"
Hi @MeganJF<\/p>
I guess you forget to use AND( ).<\/p>
For example, <\/p>
- IF([Total Value to User Score]@row = <3.8, >4.7, \"Mild,<\/li><\/ul>
should be<\/p>
- IF(AND(<\/strong>[Total Value to User Score]@row < 3.8, [Total Value to User Score]@row > 4.7)<\/strong>, \"Mild\",<\/li><\/ul>
However, the following would be more straightforward.<\/p>
- =IF([Total Value to User Score]@row >= 4.8, \"No Pain\", <\/li>
- IF([Total Value to User Score]@row >= 3.8, \"Mild\", <\/li>
- IF([Total Value to User Score]@row >= 2.8, \"Moderate\", <\/li>
- IF([Total Value to User Score]@row >= 1.8, \"Very Severe\", <\/li>
- IF([Total Value to User Score]@row < 1.8, \"Extreme\", \"//m.santa-greenland.com/community/discussion/106845/\")))))<\/li><\/ul>
- IF(AND(<\/strong>[Total Value to User Score]@row < 3.8, [Total Value to User Score]@row > 4.7)<\/strong>, \"Mild\",<\/li><\/ul>
![\"Formula](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/N18M5WZN6TYQ\/formula-to-assign-symbol-based-on-a-cell-27s-value.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
