My Count If formula doesn't work with a < in the field i need to count
I want to count the number of rows which have the <50sqm in the Implementation stage column, i don't get an error with the below formula but this also doesn't give me the correct number, it just returns a 0.
=COUNTIFS([IMPLEMENTATION STAGE]:[IMPLEMENTATION STAGE], "<50sqm", [BUILD START DATE]:[BUILD START DATE], <=DATE(2020, 12, 31), [EVENT TYPE]:[EVENT TYPE], <>"Digital", [EVENT STATUS]:[EVENT STATUS], <>"Cancelled")
this formula works with every other status i have in the same column apart from the <100sqm option.
Any ideas how i can fix this without changing the data to say "under 50 sqm"
Answers
-
David Tutwiler Overachievers Alumni
You could use CONTAINS to search for the 50sqm part. Something like:
CONTAINS("50sqm", @cell)
Help Article Resources
Categories
=COUNTIFS({Polaris minus CSC Creator}, =Creator@row, {Polaris minus CSC Date}, >=DATE(2022, 1, 1), {Polaris minus CSC Date}, <=DATE(2022, 12, 31)) + COUNTIFS(({Polaris CSC Range 2}, =Creator@row, {Polaris CSC Open}, >=DATE(2022, 1, 1), {Polaris CSC Open}, <=DATE(2022, 12, 31))<\/p>
The general concept here is to break your formula up into two different COUNTIFS statements. The first half of your formula seems to be exactly right. You should then essentially write the same formula for the second sheet and add the results of those two statements together.<\/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":111021,"type":"question","name":"Using COUNTIFS to count number of occurrences within two different sheets = UNPARSEABLE","excerpt":"Hello - This is my first time using this community. I am trying count across two sheets to count the number of times a name is present in a column. They could be contained in both or just one sheet. My formula is continually wrong and from experience I know that it is likely something little that I am missing. Here is the…","snippet":"Hello - This is my first time using this community. I am trying count across two sheets to count the number of times a name is present in a column. They could be contained in both…","categoryID":322,"dateInserted":"2023-09-29T22:39:36+00:00","dateUpdated":null,"dateLastComment":"2023-10-02T13:47:16+00:00","insertUserID":167773,"insertUser":{"userID":167773,"name":"Michele R","title":"Director","url":"https:\/\/community.smartsheet.com\/profile\/Michele%20R","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jY_1I-0k0nM!wmGyv1sRsn0!t7pDp0ylDku","dateLastActive":"2023-10-02T14:23:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":167773,"lastUser":{"userID":167773,"name":"Michele R","title":"Director","url":"https:\/\/community.smartsheet.com\/profile\/Michele%20R","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jY_1I-0k0nM!wmGyv1sRsn0!t7pDp0ylDku","dateLastActive":"2023-10-02T14:23:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":30,"score":null,"hot":3392284612,"url":"https:\/\/community.smartsheet.com\/discussion\/111021\/using-countifs-to-count-number-of-occurrences-within-two-different-sheets-unparseable","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111021\/using-countifs-to-count-number-of-occurrences-within-two-different-sheets-unparseable","format":"Rich","lastPost":{"discussionID":111021,"commentID":397839,"name":"Re: Using COUNTIFS to count number of occurrences within two different sheets = UNPARSEABLE","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397839#Comment_397839","dateInserted":"2023-10-02T13:47:16+00:00","insertUserID":167773,"insertUser":{"userID":167773,"name":"Michele R","title":"Director","url":"https:\/\/community.smartsheet.com\/profile\/Michele%20R","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jY_1I-0k0nM!wmGyv1sRsn0!t7pDp0ylDku","dateLastActive":"2023-10-02T14:23:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-02T13:47:22+00:00","dateAnswered":"2023-10-02T13:38:56+00:00","acceptedAnswers":[{"commentID":397835,"body":"
Because your syntax is off. You need to move one of the closing parenthesis from the very end to close out the first COUNTIFS before moving on to add the second one.<\/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":110978,"type":"question","name":"IndexCollect \/ JoinCollect for Multi Select Columns","excerpt":"I have one column called PRIMARY PRODUCT which is a single select dropdown and another called PRODUCTS OTHER which is a multi-select drop down. I have a third column called PROFIT CENTRE CODE which needs to populate based on all the products selected in the products columns. I have worked out a formula for PRODUCTS OTHER…","snippet":"I have one column called PRIMARY PRODUCT which is a single select dropdown and another called PRODUCTS OTHER which is a multi-select drop down. I have a third column called PROFIT…","categoryID":322,"dateInserted":"2023-09-29T08:50:47+00:00","dateUpdated":null,"dateLastComment":"2023-10-02T13:17:30+00:00","insertUserID":163620,"insertUser":{"userID":163620,"name":"Samantha McDonald","url":"https:\/\/community.smartsheet.com\/profile\/Samantha%20McDonald","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-02T13:02:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-10-02T13:35:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":38,"score":null,"hot":3392233097,"url":"https:\/\/community.smartsheet.com\/discussion\/110978\/indexcollect-joincollect-for-multi-select-columns","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110978\/indexcollect-joincollect-for-multi-select-columns","format":"Rich","lastPost":{"discussionID":110978,"commentID":397820,"name":"Re: IndexCollect \/ JoinCollect for Multi Select Columns","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397820#Comment_397820","dateInserted":"2023-10-02T13:17:30+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-10-02T13:35:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-29T14:30:55+00:00","dateAnswered":"2023-09-29T13:32:47+00:00","acceptedAnswers":[{"commentID":397633,"body":"
For your first one with the DISTINCT function, you just need to move one of the closing parenthesis from the very end to to after [PRODUCTS OTHER]@row. You need to close the DISTINCT function before you enter your delimiter for the JOIN function.<\/p>
<\/p>
For the second piece, you would \"add\" the INDEX\/COLLECT to the JOIN.<\/p>
=INDEX(...........) + \" , \" + JOIN(..........)<\/p>"},{"commentID":397706,"body":"
My suggestion would be to swap the comma out for a line break and convert the column into a multi-select dropdown column.<\/p>
=INDEX(COLLECT({PROFIT CENTRE}, {PRODUCT MATCH}, [PRIMARY PRODUCT]@row), 1) + CHAR(10)<\/strong> + JOIN(DISTINCT(COLLECT({PROFIT CENTRE}, {PRODUCT MATCH}, CONTAINS(@cell, [PRODUCTS OTHER]@row))), CHAR(10)<\/strong>)<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
