COUNTIF formula counting specific criteria under a PARENT row

Bradhickswa
Bradhickswa ✭✭
inFormulas and Functions

Hi All,

I have a shipping tracker I created for six different stages we're doing for a project. I need to create a graphical representation of the different statuses for each stage and type. The formula would read: Count of "delivered" for "POM to IHA" under "WW01, as one example. A separate formula would be Count of "In Transit" for "IHA to PA" under WW02. I've attached a picture of my table to show you what I'm referring to.

Screenshot (28).png

Thanks!

Brad

Tags:
· Share on Facebook Share on Twitter

Answers

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
@v.winters<\/a> <\/p>

If the two percent columns are formatted as percent columns (see screenshot), then the % Not Complete column would have this formula: <\/p>

=1 - [%Complete]<\/em>@row<\/p>

\n
\n \n \"Menu.PNG\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps!<\/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":107008,"type":"question","name":"COUNT CELLS BY MONTH WITHIN ONE RANGE (COUNTIFS?)","excerpt":"Hello Everyone! First time asking for help, I always found my answers within everyone else's questions, but not this time! We work off one main sheet where all our leads come in, I would like to create in another sheet a count by month of that \"created date\" column, to see how many leads came in in January, in February,…","snippet":"Hello Everyone! First time asking for help, I always found my answers within everyone else's questions, but not this time! We work off one main sheet where all our leads come in,…","categoryID":322,"dateInserted":"2023-06-28T12:43:18+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T16:36:21+00:00","insertUserID":121475,"insertUser":{"userID":121475,"name":"Dan Benitah","url":"https:\/\/community.smartsheet.com\/profile\/Dan%20Benitah","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTteTBX82EPc5KIaiSjoPiuvvmUQ_FSNi2hlGCTngDw=s96-c","dateLastActive":"2023-06-28T18:25:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-28T18:51:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":41,"score":null,"hot":3375931179,"url":"https:\/\/community.smartsheet.com\/discussion\/107008\/count-cells-by-month-within-one-range-countifs","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107008\/count-cells-by-month-within-one-range-countifs","format":"Rich","lastPost":{"discussionID":107008,"commentID":382886,"name":"Re: COUNT CELLS BY MONTH WITHIN ONE RANGE (COUNTIFS?)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382886#Comment_382886","dateInserted":"2023-06-28T16:36:21+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-28T18:51:58+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/7UUHRXPOF0WT\/screen-shot-2023-06-28-at-8-35-55-am.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screen Shot 2023-06-28 at 8.35.55 AM.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T18:29:37+00:00","dateAnswered":"2023-06-28T14:08:52+00:00","acceptedAnswers":[{"commentID":382837,"body":"

The formula from @Paul Newcome<\/a> should work, though the below would be an alternative:<\/p>

=COUNTIFS({Date created Range 1}, AND(IFERROR(YEAR(@cell), 0) = YEAR([Start Date]@row), IFERROR(MONTH(@cell), 0) = MONTH([Start Date]@row)))<\/p>

Either of these should work.<\/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":107009,"type":"question","name":"I need help with a if formula to get the $ amount total if another column = a specific item.","excerpt":"I want to make a formula in the Column \"A Total\" in the highlighted Cell, that will give me the Sum of $ from Grand Total Column if the A is in the Project column.","snippet":"I want to make a formula in the Column \"A Total\" in the highlighted Cell, that will give me the Sum of $ from Grand Total Column if the A is in the Project column.","categoryID":322,"dateInserted":"2023-06-28T12:55:48+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T16:37:28+00:00","insertUserID":162272,"insertUser":{"userID":162272,"name":"Elayne Smith","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Elayne%20Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z_neMx7Hvj4!mVDfnuqtMWQ!RH9Ly5uQ-po","dateLastActive":"2023-06-28T13:41:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-28T18:51:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":35,"score":null,"hot":3375929596,"url":"https:\/\/community.smartsheet.com\/discussion\/107009\/i-need-help-with-a-if-formula-to-get-the-amount-total-if-another-column-a-specific-item","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107009\/i-need-help-with-a-if-formula-to-get-the-amount-total-if-another-column-a-specific-item","format":"Rich","lastPost":{"discussionID":107009,"commentID":382888,"name":"Re: I need help with a if formula to get the $ amount total if another column = a specific item.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382888#Comment_382888","dateInserted":"2023-06-28T16:37:28+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-28T18:51:58+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/N80WFJ95P6OH\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T13:45:50+00:00","dateAnswered":"2023-06-28T13:05:16+00:00","acceptedAnswers":[{"commentID":382818,"body":"

Hi @Elayne Smith<\/a>,<\/p>

The formula for this would be:<\/p>

=SUMIF(Project:Project, \"A\", [Grand Total]:[Grand Total])<\/p>

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

Hope this helps - any issues etc. 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

  • How to subtract percentages?

    I created a new column % Not complete and am trying to add a formula to have =100%-[%Complete]@row. Answer #UNPARSEABLE I have also tried =100-%Complete]@row. Answer: This will populate an answer, however I cannot adjust the formatting from % to number Example: %Complete = 13% Correct answer should display 87% Second…

  • COUNT CELLS BY MONTH WITHIN ONE RANGE (COUNTIFS?)

    Hello Everyone! First time asking for help, I always found my answers within everyone else's questions, but not this time! We work off one main sheet where all our leads come in, I would like to create in another sheet a count by month of that "created date" column, to see how many leads came in in January, in February,…

  • I need help with a if formula to get the $ amount total if another column = a specific item.

    I want to make a formula in the Column "A Total" in the highlighted Cell, that will give me the Sum of $ from Grand Total Column if the A is in the Project column.