Countif and countifs not updating

罗伯·普里查德 ✭✭✭

04/05/18 edited 12/09/19 inFormulas and Functions

I have created a smartsheet with a number of different formulas in to pull together monthly figures. I then use this information to populate charts in my sights. One view I use is to count the number of new jobs added each month. This was working great until latest update.

When I look at my sight the chart is no longer there and says source data file removed. I go into the smartsheet and see invalid data type error. Click in the sheet and the data refreshes and displays itself. Go back to sight and chart now works.

Carry on using smartsheet revisit sight same problem loss of data.

Sumifs on the same sheet work perfectly all the time?

Tags:

0 ·

Comments

Shaine Greenwood Employee

04/09/18

Hi Rob,

Contact our Support teamto further troubleshoot the issue that you're experiencing with your Smartsheet dashboard.

0 · Share on Facebook Share on Twitter

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公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

Learn more

With the symbols you are using, the options would typically be represented by the text entries [Empty, Quarter, Half, Three Quarter, Full]. In this case, this should work:<\/p>

=IF(COUNTIF(CHILDREN([BallCell]@row), \"Empty\") = COUNT(CHILDREN([BallCell]@row)), \"Not Started\", IF(COUNTIF(CHILDREN([BallCell]@row), \"Full\") = COUNT(CHILDREN([BallCell]@row)), \"Completed\", \"In Progress\"))<\/p>

If you do have your sheet setup somehow so that the entries are respresented by [0, 0.25, 0.50, 0.75, 1], then this should work:<\/p>

=IF(COUNTIF(CHILDREN([BallCell]@row), < 0.25) = COUNT(CHILDREN([BallCell]@row)), \"Not Started\", IF(COUNTIF(CHILDREN([BallCell]@row), 1) = COUNT(CHILDREN([BallCell]@row)), \"Completed\", \"In Progress\"))<\/p>

In either case, you will need to substitute [BallCell] with the name of the column with your symbols. The column name is not visible in your screenshot.<\/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":106869,"type":"question","name":"Formula to Assign Symbol based on a cell's value","excerpt":"This is a complicated one that I once got to work with RGY balls, but cannot figure out how to apply this to the Pain scale emojis. What I am trying to do: (Using the pain scale symbols) Have symbols automatically changed based on the value in the corresponding cell. I am already using a formula to assign a value based on…","categoryID":322,"dateInserted":"2023-06-24T20:02:20+00:00","dateUpdated":null,"dateLastComment":"2023-06-25T22:29:54+00:00","insertUserID":158092,"insertUser":{"userID":158092,"name":"MeganJF","url":"https:\/\/community.smartsheet.com\/profile\/MeganJF","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DgFow9e-1vo!YfMmmJOUTjo!UDpVBcB6ikY","dateLastActive":"2023-06-25T17:43:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":139601,"lastUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-26T04:19:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":27,"score":null,"hot":3375372134,"url":"https:\/\/community.smartsheet.com\/discussion\/106869\/formula-to-assign-symbol-based-on-a-cells-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106869\/formula-to-assign-symbol-based-on-a-cells-value","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106869,"commentID":382193,"name":"Re: Formula to Assign Symbol based on a cell's value","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382193#Comment_382193","dateInserted":"2023-06-25T22:29:54+00:00","insertUserID":139601,"insertUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-26T04:19:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-25T17:10:13+00:00","dateAnswered":"2023-06-25T03:32:30+00:00","acceptedAnswers":[{"commentID":382165,"body":"

Hi @MeganJF<\/p>

I guess you forget to use AND( ).<\/p>

For example, <\/p>

IF([Total Value to User Score]@row = <3.8, >4.7, \"Mild,<\/li><\/ul>
should be<\/p>
- IF(AND(<\/strong>[Total Value to User Score]@row < 3.8, [Total Value to User Score]@row > 4.7)<\/strong>, \"Mild\",<\/li><\/ul>
  However, the following would be more straightforward.<\/p>
  =IF([Total Value to User Score]@row >= 4.8, \"No Pain\", <\/li>
  IF([Total Value to User Score]@row >= 3.8, \"Mild\", <\/li>
  IF([Total Value to User Score]@row >= 2.8, \"Moderate\", <\/li>
  IF([Total Value to User Score]@row >= 1.8, \"Very Severe\", <\/li>
  IF([Total Value to User Score]@row < 1.8, \"Extreme\", \"//www.santa-greenland.com/community/discussion/22941/\")))))<\/li><\/ul>
  \n
  \n \n $\"Formula$ <\/img><\/a>\n <\/div>\n<\/div>\n
  
  <\/p>
  Please take a look at the published sheet, as well.<\/p>
  \n \n https:\/\/app.smartsheet.com\/b\/publish?EQBCT=ef585943fa254224991d3e1a0762da5b\n <\/a>\n<\/div>
  
  <\/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":106858,"type":"question","name":"Only copy certain columns to a new sheet?","excerpt":"I am trying to marry two sheets together, one is a new sheet for ticket intake and the other sheet has been in use by another team that has ticket information for one person but also additional information for their team. I don't want the team to have to enter in the data into the intake form in addition to their form. I…","categoryID":322,"dateInserted":"2023-06-23T21:19:12+00:00","dateUpdated":null,"dateLastComment":"2023-06-24T22:11:24+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T21:35:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-25T21:52:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":31,"score":null,"hot":3375201636,"url":"https:\/\/community.smartsheet.com\/discussion\/106858\/only-copy-certain-columns-to-a-new-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106858\/only-copy-certain-columns-to-a-new-sheet","format":"Rich","lastPost":{"discussionID":106858,"commentID":382162,"name":"Re: Only copy certain columns to a new sheet?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382162#Comment_382162","dateInserted":"2023-06-24T22:11:24+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-25T21:52:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-23T21:36:51+00:00","dateAnswered":"2023-06-23T21:25:16+00:00","acceptedAnswers":[{"commentID":382124,"body":"
  @Pamela Wagner<\/a> There's nothing in core Smartsheet. <\/p>
  If you have a unique ID (like ticket number) that is in both sheets, you could use DataMesh if you have access to that or cross sheet formulas to pull in the data you want based on the matching unique ID.<\/p>
  Otherwise, you'd have to use an Add-on. If you are interested in hearing about an add-on that can do this, send me a message at https:\/\/smartsheetguru.com\/contact<\/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":[]}">