using COUNTIF to find exact numbers.

I've been using COUNTIF to count the number of occurrences in a month.

So i've got a list of 'events' throughout the year, all with different dates.

I'm wanting to count how many are in each month. What I've done is to use the MONTH formula in another column to give a numerical value of the month in 1 - 12.

And then using a formula to count how many 1's, how many 2's etc. This tells me how many occurrences there are in each month.

=COUNTIF({Improve Data Range 2}, FIND("1", @cell) > 0)


However I noticed the total number was getting a bit high, and realised that my formula for finding '1' was also counting the 1 in 10, so was counting all of Octobers occurrences. I guess by this logic it would also count the 1 in 11, and 12.

Is there to make this formula only identify a singular 1, and not pick up a 1 in a bigger number?

Alternatively is there a way to make the MONTH formula spit out a Month name such as 'Jan' 'Feb' rather than a numerical value? That would be a unique value that countif couldn't mix up.


Thanks

Best Answer

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    Normally, I'm in favor of splitting out functions so they don't get too messy. However, I think you're best bet will be to add the MONTH() function into this function to make sure you get the right month. Something like:

    =COUNTIF({Improve Data Range 3}, MONTH(@cell) = 1)

    This assumes that Improve Data Range 3 is the column that contains the date.

  • Brilliant thanks. Took me a few tried to get it working, but seems to work a treat.

  • I'm actually having some issues with cross referencing the date column from another sheet. I'm using:

    =COUNTIFS({impdate}, <>. MONTH(@cell) = 1)

    it keep returning #INVALID DATA TYPE.

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    Have you tried:

    =COUNTIFS({impdate}, NOT(MONTH(@cell)=1))

    If that doesn't work, you'll want to look at your column type to make sure it's a date format.

  • Thanks,

    It is still coming up with the error. It seems the MONTH function doesn't like empty cells when Cross linked to other sheets. So the empty 10 cells that appear at the bottom of the sheet are causing the error.

    I need a way for it to ignore cells that don't contain dates. A sort of ISDATE = True function, but I'm not sure how to construct that within the formula.

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    You could do:

    =COUNTIFS({impdate}, NOT(MONTH(@cell)=1), {impdate}, NOT(ISBLANK()))

帮助文章资源欧宝体育app官方888

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
You would use something like this:<\/p>

=IF(AND([Average Score]@row>= 5, [Average Score]@row<= 9), \"Project\", IF(AND([Average Score]@row>= 10, [Average Score]@row<= 15), \"Program\"))<\/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":110985,"type":"question","name":"Dashboard Last Updated Date","excerpt":"I would like to display the last time the dashboard was updated. My approach was adding a date field in the summary data that automatically pulls the last saved date of the sheet. I don't think I can do a formula in the date field type. Any other suggestions?","snippet":"I would like to display the last time the dashboard was updated. My approach was adding a date field in the summary data that automatically pulls the last saved date of the sheet.…","categoryID":322,"dateInserted":"2023-09-29T12:56:38+00:00","dateUpdated":"2023-09-29T23:58:27+00:00","dateLastComment":"2023-09-29T16:19:19+00:00","insertUserID":167704,"insertUser":{"userID":167704,"name":"Jenn Moffett","title":"Director, PMO","url":"https:\/\/community.smartsheet.com\/profile\/Jenn%20Moffett","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T15:38:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":166028,"lastUserID":167704,"lastUser":{"userID":167704,"name":"Jenn Moffett","title":"Director, PMO","url":"https:\/\/community.smartsheet.com\/profile\/Jenn%20Moffett","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T15:38:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3391997757,"url":"https:\/\/community.smartsheet.com\/discussion\/110985\/dashboard-last-updated-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110985\/dashboard-last-updated-date","format":"Rich","tagIDs":[292,335],"lastPost":{"discussionID":110985,"commentID":397701,"name":"Re: Dashboard Last Updated Date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397701#Comment_397701","dateInserted":"2023-09-29T16:19:19+00:00","insertUserID":167704,"insertUser":{"userID":167704,"name":"Jenn Moffett","title":"Director, PMO","url":"https:\/\/community.smartsheet.com\/profile\/Jenn%20Moffett","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T15:38:19+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-29T15:39:37+00:00","dateAnswered":"2023-09-29T14:10:25+00:00","acceptedAnswers":[{"commentID":397662,"body":"

Hi @Jenn Moffett<\/a>,<\/p>

One approach is as follows.<\/p>

  1. Create a helper column. Name it \"Modified\", and select \"Modified Date\" as the column type. You can optionally hide this column.<\/li>
  2. Create a Sheet Summary field. Name is \"Last Updated\", and select \"Date\" as the column type.<\/li>
  3. Type the following formula into the \"Last Updated\" Sheet Summary field: =MAX(Modified:Modified)<\/li><\/ol>

    Hope that helps! I just tested it before recommending, and it worked like a charm! :)<\/p>

    BRgds,<\/p>

    -Ray<\/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":292,"urlcode":"dashboards","name":"Dashboards"},{"tagID":335,"urlcode":"sheet-summary","name":"Sheet Summary"}]},{"discussionID":110981,"type":"question","name":"Counting yes and yes w\/notes","excerpt":"Hello, I am having trouble counting using the COUNTIFS formula to yes's and another category called \"Yes w\/notes\". Here is the current formula that I have: • =COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is there a permit required?]@row, @cell = \"Yes\") \/ COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is…","snippet":"Hello, I am having trouble counting using the COUNTIFS formula to yes's and another category called \"Yes w\/notes\". Here is the current formula that I have: • =COUNTIFS([Are all…","categoryID":322,"dateInserted":"2023-09-29T12:04:09+00:00","dateUpdated":null,"dateLastComment":"2023-09-29T16:34:36+00:00","insertUserID":148583,"insertUser":{"userID":148583,"name":"ksandoval","url":"https:\/\/community.smartsheet.com\/profile\/ksandoval","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T16:57:40+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-09-29T16:56:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":17,"score":null,"hot":3391996125,"url":"https:\/\/community.smartsheet.com\/discussion\/110981\/counting-yes-and-yes-w-notes","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110981\/counting-yes-and-yes-w-notes","format":"Rich","lastPost":{"discussionID":110981,"commentID":397702,"name":"Re: Counting yes and yes w\/notes","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397702#Comment_397702","dateInserted":"2023-09-29T16:34:36+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-09-29T16:56: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-29T13:48:27+00:00","dateAnswered":"2023-09-29T13:35:30+00:00","acceptedAnswers":[{"commentID":397634,"body":"

    Try this:<\/p>

    =COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is there a permit required?]@row, OR(@cell = \"Yes\", @cell = \"Yes w\/Notes\")) \/ COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is there a permit required?]@row, @cell <> \"n\/a\")<\/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":[]}">

    Trending in Formulas and Functions