@TravisClem<\/a> ,<\/p>I'm not sure what you are using for the formula in the \"ALL\" column, but if it's the same as the first section of your posted formula, I would think you are getting the wrong results as it would count every year up to the year in question (as opposed to only the year in question).<\/p>
Either way, try this for the USA column.<\/p>
=COUNTIFS({Projects In by Date}, <=DATE([Primary Column]@row, 12, 31), {Projects In by Date}, >=DATE([Primary Column]@row, 1, 1), {State}, <>\"CAN-Canada\", {State}, <>\"OC-Outside North America\")<\/p>
The formula I have posted ensures only 1 year is looked at - use the first portion if it does turn out your other formula is incorrect.<\/p>
Hope this helps,<\/p>
Dave<\/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":111199,"type":"question","name":"AVERAGEIF Formula Help","excerpt":"I'm trying to come up with a summary sheet formula that summarizes an employee's average evaluation score. I'm working with two columns: Employee Name and Overall Satisfaction. Let's say their name is Slim Jones. The formula I came up with is: =AVERAGEIF(Employee Name:Employee Name, \"Slim Jones\", [Overall…","snippet":"I'm trying to come up with a summary sheet formula that summarizes an employee's average evaluation score. I'm working with two columns: Employee Name and Overall Satisfaction.…","categoryID":322,"dateInserted":"2023-10-04T18:15:15+00:00","dateUpdated":"2023-10-04T18:17:39+00:00","dateLastComment":"2023-10-05T11:08:22+00:00","insertUserID":167984,"insertUser":{"userID":167984,"name":"amy_ilearning","title":"PD Operations Manager","url":"https:\/\/community.smartsheet.com\/profile\/amy_ilearning","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-05T11:14:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":167984,"lastUserID":167984,"lastUser":{"userID":167984,"name":"amy_ilearning","title":"PD Operations Manager","url":"https:\/\/community.smartsheet.com\/profile\/amy_ilearning","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-05T11:14:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":19,"score":null,"hot":3392948617,"url":"https:\/\/community.smartsheet.com\/discussion\/111199\/averageif-formula-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111199\/averageif-formula-help","format":"Rich","lastPost":{"discussionID":111199,"commentID":398549,"name":"Re: AVERAGEIF Formula Help","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398549#Comment_398549","dateInserted":"2023-10-05T11:08:22+00:00","insertUserID":167984,"insertUser":{"userID":167984,"name":"amy_ilearning","title":"PD Operations Manager","url":"https:\/\/community.smartsheet.com\/profile\/amy_ilearning","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-05T11:14:12+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-05T11:27:35+00:00","dateAnswered":"2023-10-04T23:06:55+00:00","acceptedAnswers":[{"commentID":398506,"body":"
When a column name has spaces, numbers, and\/or special characters, you need to use square brackets around them in a formula.<\/p>
Try wrapping each [Employee Name] column reference in square brackets the same way you wrapped the [Overall Satisfaction] column names in square brackets.<\/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":111221,"type":"question","name":"How to enter the word \"T METAL\" in a different column automatically","excerpt":"How to enter the word \"T METAL\" in a different column automatically when another column has an entry on the same line. If someone enters a random number in a row on a column named \"GA\", I would like \"T METAL\" to automatically populate another column named \"GP Macro\" on the same row. Can this be done? Thanks!!","snippet":"How to enter the word \"T METAL\" in a different column automatically when another column has an entry on the same line. If someone enters a random number in a row on a column named…","categoryID":322,"dateInserted":"2023-10-04T20:48:52+00:00","dateUpdated":null,"dateLastComment":"2023-10-04T22:29:02+00:00","insertUserID":151437,"insertUser":{"userID":151437,"name":"Jim Potter","url":"https:\/\/community.smartsheet.com\/profile\/Jim%20Potter","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ngjP_wybFrY!jhjMYwzQ1zY!jxOeq2NkJxR","dateLastActive":"2023-10-05T12:23:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":165153,"lastUser":{"userID":165153,"name":"DKazatsky2","url":"https:\/\/community.smartsheet.com\/profile\/DKazatsky2","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-04T22:29:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":21,"score":null,"hot":3392911674,"url":"https:\/\/community.smartsheet.com\/discussion\/111221\/how-to-enter-the-word-t-metal-in-a-different-column-automatically","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111221\/how-to-enter-the-word-t-metal-in-a-different-column-automatically","format":"Rich","lastPost":{"discussionID":111221,"commentID":398498,"name":"Re: How to enter the word \"T METAL\" in a different column automatically","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398498#Comment_398498","dateInserted":"2023-10-04T22:29:02+00:00","insertUserID":165153,"insertUser":{"userID":165153,"name":"DKazatsky2","url":"https:\/\/community.smartsheet.com\/profile\/DKazatsky2","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-04T22:29:16+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-05T12:23:48+00:00","dateAnswered":"2023-10-04T22:29:02+00:00","acceptedAnswers":[{"commentID":398498,"body":"
Hi @Jim Potter,<\/p>
You can use this in the GP Macro column.<\/p>
=IF(ISNUMBER(GA@row), \"T METAL\")<\/p>
Hope this helps,<\/p>
Dave<\/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
Stack CountIFS within one cell HI, I'm trying to produce a table that contains projects received within a year combined, then separated into regions. Below is the formula I'm using, I can only guess that it's subtracting the 345 from the 2106 instead of only counting the USA projects within the 345. =COUNTIF({Projects In by Date}, <=DATE([Primary…
AVERAGEIF Formula Help I'm trying to come up with a summary sheet formula that summarizes an employee's average evaluation score. I'm working with two columns: Employee Name and Overall Satisfaction. Let's say their name is Slim Jones. The formula I came up with is: =AVERAGEIF(Employee Name:Employee Name, "Slim Jones", [Overall…
How to enter the word "T METAL" in a different column automatically How to enter the word "T METAL" in a different column automatically when another column has an entry on the same line. If someone enters a random number in a row on a column named "GA", I would like "T METAL" to automatically populate another column named "GP Macro" on the same row. Can this be done? Thanks!!
Nic Larsen ✭✭✭✭✭✭
