COUNTIFS formula not returning result

Sara Morris
Sara Morris ✭
edited 12/09/19 inFormulas and Functions

Hello Community!

I have a COUNTIFS formula that does not return an error, but it also does not return the result I am expecting. Long story short, I am trying to count how many projects we plan to start by month and year. There are three separate columns that I am pulling from. The column that is giving me troubles is the Year. It does not seem to matter if I put in a static number or a cell reference, it will not count. If I take the year out of the equation, it works fine.

I have included a screen shot that outlines what my data looks like and my formulas. I wondered if it has to do with the formula I am using to pull out the year information, but I am using the same thing for month with no issue. Any ideas on what could be causing the return of zero would be greatly appreciated.



Thanks!

Sara

Formula Info.PNG

Tags:
· Share on Facebook Share on Twitter

Comments

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
That formula looks good. It needs to be in the email cell - where you want the email to appear. The yellow ones here:<\/p>
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Can you try the cross reference again? Double click on the formula and put your cursor onto the reference like this:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Then click \"Edit Reference\" on the pop up.<\/p>

Make sure you have highlighted the two columns you need, that the email address is in the second one, and your \"Sheet reference name\" looks exactly <\/strong>like this <\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

🤞<\/span><\/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":112325,"type":"question","name":"Help with #Unparseable Formulas","excerpt":"Hi Everyone! I am a new Smartsheet user and am having some issues creating formulas that are based off of multiple columns where those columns are set up as drop downs with multiple selections. My Formula: =SUMIFS({Deliverables}, {Work Type} HAS (@cell, Reprint), {Fiscal Quarter}, HAS (@cell, \"Q1\"), {Fiscal Year}, HAS…","snippet":"Hi Everyone! I am a new Smartsheet user and am having some issues creating formulas that are based off of multiple columns where those columns are set up as drop downs with…","categoryID":322,"dateInserted":"2023-10-30T14:18:55+00:00","dateUpdated":null,"dateLastComment":"2023-10-30T15:33:41+00:00","insertUserID":169246,"insertUser":{"userID":169246,"name":"jimhmeyers","url":"https:\/\/community.smartsheet.com\/profile\/jimhmeyers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-30T15:51:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":169246,"lastUser":{"userID":169246,"name":"jimhmeyers","url":"https:\/\/community.smartsheet.com\/profile\/jimhmeyers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-30T15:51:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":57,"score":null,"hot":3397359156,"url":"https:\/\/community.smartsheet.com\/discussion\/112325\/help-with-unparseable-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112325\/help-with-unparseable-formulas","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":112325,"commentID":402230,"name":"Re: Help with #Unparseable Formulas","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402230#Comment_402230","dateInserted":"2023-10-30T15:33:41+00:00","insertUserID":169246,"insertUser":{"userID":169246,"name":"jimhmeyers","url":"https:\/\/community.smartsheet.com\/profile\/jimhmeyers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-30T15:51: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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-30T15:03:11+00:00","dateAnswered":"2023-10-30T14:53:42+00:00","acceptedAnswers":[{"commentID":402218,"body":"

I don't know if the extra spaces between HAS and your open parens would cause an issue, but you are also missing quotes around Reprint.<\/p>

=SUMIFS({Deliverables}, {Work Type}, HAS(@cell, \"Reprint\"), {Fiscal Quarter}, HAS(@cell, \"Q1\"), {Fiscal Year}, HAS(@cell, \"FY24\"))<\/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":112320,"type":"question","name":"return date which is next higher one","excerpt":"Hi, I'm looking for a formula to check the \"End date\" and return the next higher from date from the reference sheet. e.g. End Date is May 7, the result should be Pre-confirmed 90 LT=May 8 or End Date is June 16, the result should be Pre-confirmed 90 LT=June 26 I have no idea which formula could work. Thanks for your help!","snippet":"Hi, I'm looking for a formula to check the \"End date\" and return the next higher from date from the reference sheet. e.g. End Date is May 7, the result should be Pre-confirmed 90…","categoryID":322,"dateInserted":"2023-10-30T08:05:20+00:00","dateUpdated":null,"dateLastComment":"2023-10-30T12:45:44+00:00","insertUserID":131273,"insertUser":{"userID":131273,"name":"Christine Menke","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Menke","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!!wxtct5RkPno!3WtdPxB0jLw!Ul_V8Bw57zI","dateLastActive":"2023-10-30T12:53:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-30T17:19:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":36,"score":null,"hot":3397326064,"url":"https:\/\/community.smartsheet.com\/discussion\/112320\/return-date-which-is-next-higher-one","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112320\/return-date-which-is-next-higher-one","format":"Rich","lastPost":{"discussionID":112320,"commentID":402209,"name":"Re: return date which is next higher one","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402209#Comment_402209","dateInserted":"2023-10-30T12:45:44+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-30T17:19:41+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\/19AUMWBF6VWT\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/19AUMWBF6VWT\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/19AUMWBF6VWT\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/19AUMWBF6VWT\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/19AUMWBF6VWT\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/19AUMWBF6VWT\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-31T05:02:35+00:00","dateAnswered":"2023-10-30T10:26:53+00:00","acceptedAnswers":[{"commentID":402191,"body":"

Hi @Christine Menke<\/a>,<\/p>

A formula like this should work:<\/p>

=INDEX([Pre-confirmed 90 LT]:[Pre-confirmed 90 LT], MATCH([End Date]@row, [Pre-confirmed 90 LT]:[Pre-confirmed 90 LT]) + 1)<\/p>

Some example data:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps, but if you've any problems\/questions, then just post! 🙂<\/span><\/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

  • Struggling with Formula. Not sure if using correct one.

    I have 2 sheets. We'll call Sheet A "Roster" and Sheet B "Grading Sheet" Sheet A has columns for "Employee Number" and "Email Address" What I am trying to do is on Sheet B, have a column in which you can enter the employee number, it reference Sheet A and then it will automatically enter the email address associated with…

  • Help with #Unparseable Formulas

    Hi Everyone! I am a new Smartsheet user and am having some issues creating formulas that are based off of multiple columns where those columns are set up as drop downs with multiple selections. My Formula: =SUMIFS({Deliverables}, {Work Type} HAS (@cell, Reprint), {Fiscal Quarter}, HAS (@cell, "Q1"), {Fiscal Year}, HAS…

  • return date which is next higher one

    Hi, I'm looking for a formula to check the "End date" and return the next higher from date from the reference sheet. e.g. End Date is May 7, the result should be Pre-confirmed 90 LT=May 8 or End Date is June 16, the result should be Pre-confirmed 90 LT=June 26 I have no idea which formula could work. Thanks for your help!