SUMIFS help in Summary with column headers containing special chars
I'm trying to get the below SUMIFS to work but i'm getting #UNPARSEABLE.
=SUMIFS(Requested Amount (in Local Currency):Requested Amount (in Local Currency), Budget Owner*:Budget Owner*, "Tom MacKay", Approved:Approved, = 1)
The column names contain special characters, is that what is causing the problem? Or do I just have the syntax all wrong?
Best Answer
-
Stefan ✭✭✭✭✭
Hello,
using special characters in the column titles is not a good idea.
Anyway, what you are missing is, that column titles in formulas always need to have square brackets around them, if they do not consist of one word.
So try:
=SUMIFS([Requested Amount (in Local Currency)]:[Requested Amount (in Local Currency)], [Budget Owner*]:[Budget Owner*], "Tom MacKay", Approved:Approved, = 1)
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
Answers
-
Stefan ✭✭✭✭✭
Hello,
using special characters in the column titles is not a good idea.
Anyway, what you are missing is, that column titles in formulas always need to have square brackets around them, if they do not consist of one word.
So try:
=SUMIFS([Requested Amount (in Local Currency)]:[Requested Amount (in Local Currency)], [Budget Owner*]:[Budget Owner*], "Tom MacKay", Approved:Approved, = 1)
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
garcias76986 ✭✭✭Stefan it was perfect!!! Thanks so much!!!
Help Article Resources
Categories
For logging changes on a particular sheet, I use an automation that copies the entire row to a different sheet used to log the data changes. The automation is triggered whenever a row is modified or added. One of the columns I have in my main sheet is a system column called Modified and the column type is \"Modified Date\".<\/p>
I then use reports or cross sheet links to create the view of the data that I want from the log file.<\/p>
Hope this is helpful for you and have a great day.<\/p>
Tom<\/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":101730,"type":"question","name":"COUNTIFS with CHILDREN and Status Bar","excerpt":"I am trying to count the number of child rows that have a status bar symbol of Quarter, Half, or Three Quarters. Example: if 2 child rows are half, one is full, one is empty, and one is three quarters, the count should be 3 to account for the two halfs and one three quarters, and negate the empty and full ones. Just trying…","categoryID":322,"dateInserted":"2023-02-24T17:22:04+00:00","dateUpdated":null,"dateLastComment":"2023-02-24T17:34:41+00:00","insertUserID":138915,"insertUser":{"userID":138915,"name":"Kristina S Otten","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kristina%20S%20Otten","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xJEmseeh63s!aSEn2qecc_s!4EeZbPMRxDk","dateLastActive":"2023-02-24T17:44:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":86866,"lastUser":{"userID":86866,"name":"Jeff Reisman","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jeff%20Reisman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/3V8W2AM105QE\/nIDXI4I7VDS7W.JPG","dateLastActive":"2023-02-24T21:37:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":15,"score":null,"hot":3354521205,"url":"https:\/\/community.smartsheet.com\/discussion\/101730\/countifs-with-children-and-status-bar","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/101730\/countifs-with-children-and-status-bar","format":"Rich","lastPost":{"discussionID":101730,"commentID":364317,"name":"Re: COUNTIFS with CHILDREN and Status Bar","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/364317#Comment_364317","dateInserted":"2023-02-24T17:34:41+00:00","insertUserID":86866,"insertUser":{"userID":86866,"name":"Jeff Reisman","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jeff%20Reisman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/3V8W2AM105QE\/nIDXI4I7VDS7W.JPG","dateLastActive":"2023-02-24T21:37:00+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-02-24T17:33:38+00:00","dateAnswered":"2023-02-24T17:27:39+00:00","acceptedAnswers":[{"commentID":364314,"body":"
Here you go, try this:<\/p>
=COUNTIFS(CHILDREN(Progress@row), OR(@cell = \"Quarter\", @cell = \"Half\", @cell = \"Three Quarter\"))<\/p>"},{"commentID":364317,"body":"
