Formula to count number of items if it matches both headings.

Hello,

I am currently stuck on this formula. I'm not sure what I'm doing wrong but the formula keeps on providing a return of "1" for everything, when I know I have "2" counts of items which are under category 1 and have a status of in progress.

=COUNT(COLLECT({Status},[email protected], {Project Category}, [Category 1]$1))

image.png

Any advice would be appreciated! Thank you!!

Best Answer

Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭
    edited 05/26/23

    Try updating your formula to

    =COUNT(COLLECT({Status}, {Status},[email protected], {Project Category},[Category 1]$1))

    Your COLLECT() function should begin with 2 ranges, one to specify the range you want to collect (then count), then the first range to want to use as criteria. I'm surprised it's returning 1, from what I see you should be return #UNPARSEABLE error with that. Let me know if adding the addition {Status} range fixes it!

    You could also use a COUNTIFS() function if the COLLECT() keeps giving you problems.

    Jason Tarpinian -Sevan Technology

    Smartsheet Platinum Partner

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Answer ✓

    Not sure you need "COLLECT" here (or the fixed reference to the Label column).

    Try this instead: =COUNTIFS({Status},[email protected], {Project Category}, [Category 1]$1)

    I created some sample data (including a cross-sheet), and this worked.

  • Joyce W
    Joyce W ✭✭

    You are awesome! It worked! Thank you!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Cracked it, just took a little more time:<\/p>


<\/p>

=IF(Status1 = \"\", \"\", IF(Status1 = \"Full\", \"Green\", IF([Completion Date]1 < TODAY(), \"Red\", IF(AND([Completion Date]1 >= TODAY(), [Completion Date]1 < TODAY(+7)), \"Yellow\", \"Green\"))))<\/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":108000,"type":"question","name":"Sumifs with references and dates not calculating","excerpt":"HELP!!!! I am working in a summary metrics sheet. I am trying to find the total projected revenue by month. I have created 2 references, the revenue column to sum, and the date column. For some reason I keep getting 0 even though there is revenue in the column for that date. I can't figure out what is wrong with my…","snippet":"HELP!!!! I am working in a summary metrics sheet. I am trying to find the total projected revenue by month. I have created 2 references, the revenue column to sum, and the date…","categoryID":322,"dateInserted":"2023-07-24T21:59:21+00:00","dateUpdated":null,"dateLastComment":"2023-07-24T22:33:39+00:00","insertUserID":149858,"insertUser":{"userID":149858,"name":"VTwyford","url":"https:\/\/community.smartsheet.com\/profile\/VTwyford","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-24T22:26:44+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-25T12:35:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":20,"score":null,"hot":3380475780,"url":"https:\/\/community.smartsheet.com\/discussion\/108000\/sumifs-with-references-and-dates-not-calculating","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108000\/sumifs-with-references-and-dates-not-calculating","format":"Rich","lastPost":{"discussionID":108000,"commentID":386750,"name":"Re: Sumifs with references and dates not calculating","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386750#Comment_386750","dateInserted":"2023-07-24T22:33:39+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-25T12:35:24+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-07-25T09:21:15+00:00","dateAnswered":"2023-07-24T22:26:13+00:00","acceptedAnswers":[{"commentID":386748,"body":"

As written, you will not get any matches. Your formula is looking for a date that is both less than or equal to January First and Greater than or equal to January 31st. You will need to reverse those to match January numbers.<\/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":107987,"type":"question","name":"Assistance with JOIN\/INDEX and COLLECT function?","excerpt":"Hey Community! What I'm looking to do: I have Part Number and Key Supplier as 2 different columns. When I put in multiple part numbers (the first column), I want to return all of the associated suppliers (column 2). Has anyone done this before?","snippet":"Hey Community! What I'm looking to do: I have Part Number and Key Supplier as 2 different columns. When I put in multiple part numbers (the first column), I want to return all of…","categoryID":322,"dateInserted":"2023-07-24T17:50:18+00:00","dateUpdated":null,"dateLastComment":"2023-07-24T18:16:55+00:00","insertUserID":163406,"insertUser":{"userID":163406,"name":"TheNickOdeh","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/TheNickOdeh","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!By_fAfxm434!hJ_fYfgw6vw!ljAe2NhOVY8","dateLastActive":"2023-07-24T18:14:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163406,"lastUser":{"userID":163406,"name":"TheNickOdeh","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/TheNickOdeh","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!By_fAfxm434!hJ_fYfgw6vw!ljAe2NhOVY8","dateLastActive":"2023-07-24T18:14:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":33,"score":null,"hot":3380444833,"url":"https:\/\/community.smartsheet.com\/discussion\/107987\/assistance-with-join-index-and-collect-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107987\/assistance-with-join-index-and-collect-function","format":"Rich","tagIDs":[254,391],"lastPost":{"discussionID":107987,"commentID":386698,"name":"Re: Assistance with JOIN\/INDEX and COLLECT function?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386698#Comment_386698","dateInserted":"2023-07-24T18:16:55+00:00","insertUserID":163406,"insertUser":{"userID":163406,"name":"TheNickOdeh","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/TheNickOdeh","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!By_fAfxm434!hJ_fYfgw6vw!ljAe2NhOVY8","dateLastActive":"2023-07-24T18:14:29+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/1SMLREBBS48U\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-24T18:16:44+00:00","dateAnswered":"2023-07-24T18:08:43+00:00","acceptedAnswers":[{"commentID":386696,"body":"

=JOIN(COLLECT([Key Supplier]:[Key Supplier], [Part Number]:[Part Number], HAS([Part Numbers]@row, @cell)), \", \")<\/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"},{"tagID":391,"urlcode":"product-development","name":"Product Development"}]}],"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":[]}">

Trending in Formulas and Functions