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
-
David Joyeuse ✭✭✭✭✭
Hi@GrahamR
Why didn't you use this formula in your COUNTIF:
=COUNTIFS({Date Range first cell}, MONTH(@cell)= 1) for January and so on...
That will count dates that suits the MONTH you want to count. You don't really need the helper column here.
Also, there's no way MONTH will return a string. If you want to do that, you're gonna need to use an helper sheet with some INDEX/MATCH to link each number to the desired string.
Hope it helped!
Answers
-
David Joyeuse ✭✭✭✭✭
Hi@GrahamR
Why didn't you use this formula in your COUNTIF:
=COUNTIFS({Date Range first cell}, MONTH(@cell)= 1) for January and so on...
That will count dates that suits the MONTH you want to count. You don't really need the helper column here.
Also, there's no way MONTH will return a string. If you want to do that, you're gonna need to use an helper sheet with some INDEX/MATCH to link each number to the desired string.
Hope it helped!
-
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 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 Overachievers Alumni
You could do:
=COUNTIFS({impdate}, NOT(MONTH(@cell)=1), {impdate}, NOT(ISBLANK()))
帮助文章资源欧宝体育app官方888
Categories
=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":"