COUNTIFS on the same column and COUNTIFS on other columns

Lori Khoury
Lori Khoury ✭✭✭✭✭
edited 12/09/19 inFormulas and Functions

Hi

I am trying perform the COUNTIFS function and can't get this to work. So what I need to count is the following

  1. Count if criteria is "s" in the function column
  2. Count if criteria is "Med" in the priority column
  3. Count if criteria is "No" in theproject column
  4. And from thesame project column I need to count if criteria is "partial" also.

My formula has step 1-3 but I can't figure out how to include step 4.

=COUNTIFS(Functions2:Functions290, "s", Priority2:Priority290, "Med", Project2:Project290, "No")

Any chance someone knows how to do this? Criteria from multiple columns and then 2 criteria from the same column.

Any help would be greatly appreciated.

Thanks

Tags:
· Share on Facebook Share on Twitter

Comments

  • ricki
    ricki ✭✭✭✭✭✭

    How can your project field say both No and partial at the same time? That may be the issue here? are you trying to count if it either has the word no or the word partial?

    Did you try?

    =COUNTIFS(Functions2:Functions290, "s", Priority2:Priority290, "Med", Project2:Project290, "No",Project2:Project290, "partial")

    · Share on Facebook Share on Twitter
  • Lori Khoury
    Lori Khoury ✭✭✭✭✭

    Hi thanks for responding. Yes I tried that and it didn't work. Do you have any other ideas?

    · Share on Facebook Share on Twitter
  • ricki
    ricki ✭✭✭✭✭✭

    I don't really understand what you are trying to do. You have a project field. Either it will say "No" or it will say "partial" if it is only one word it cannot say both at the same time. What are you really trying to count

    · Share on Facebook Share on Twitter
  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    What you need to do is add two countifs together. The first one would check for the requirements plus NO, and the second would count the requirements plus the Partial. Try this one.

    =COUNTIFS(Functions2:Functions290, "s", Priority2:Priority290, "Med", Project2:Project290, "No") + COUNTIFS(Functions2:Functions290, "s", Priority2:Priority290, "Med", Project2:Project290, "Partial")

    Basically, you adding the result of two countifs together.

    · Share on Facebook Share on Twitter
  • Lori Khoury
    Lori Khoury ✭✭✭✭✭

    It worked and it makes total sense now that I see the formula. Thank you so much. I am just learning how to do all the formulas. I really appreciate your help.

    Thank you!!!!!

    · Share on Facebook Share on Twitter
  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Glad I could be of help!:)

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

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
you need the last if to be <=1 then Full<\/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":111351,"type":"question","name":"If or Isblank Formula","excerpt":"I am trying to write this formula, if the cell is blank return value of 0, OR if the cell contains the word resource return value of 0, otherwise return the value of 3 for both. I have written this formula 500 times today and cannot get it right, can someone help? =IF(OR(ISBLANK(Title@row), 0, 3), (Title@row = \"Resource\"),…","snippet":"I am trying to write this formula, if the cell is blank return value of 0, OR if the cell contains the word resource return value of 0, otherwise return the value of 3 for both. I…","categoryID":322,"dateInserted":"2023-10-06T19:39:59+00:00","dateUpdated":null,"dateLastComment":"2023-10-06T20:14:14+00:00","insertUserID":132094,"insertUser":{"userID":132094,"name":"Renae Gould","url":"https:\/\/community.smartsheet.com\/profile\/Renae%20Gould","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!6VVQ-oCrcYQ!xZdesVKV87w!18ODegDWWxO","dateLastActive":"2023-10-06T20:12:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":132094,"lastUser":{"userID":132094,"name":"Renae Gould","url":"https:\/\/community.smartsheet.com\/profile\/Renae%20Gould","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!6VVQ-oCrcYQ!xZdesVKV87w!18ODegDWWxO","dateLastActive":"2023-10-06T20:12:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":21,"score":null,"hot":3393245653,"url":"https:\/\/community.smartsheet.com\/discussion\/111351\/if-or-isblank-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111351\/if-or-isblank-formula","format":"Rich","lastPost":{"discussionID":111351,"commentID":398982,"name":"Re: If or Isblank Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398982#Comment_398982","dateInserted":"2023-10-06T20:14:14+00:00","insertUserID":132094,"insertUser":{"userID":132094,"name":"Renae Gould","url":"https:\/\/community.smartsheet.com\/profile\/Renae%20Gould","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!6VVQ-oCrcYQ!xZdesVKV87w!18ODegDWWxO","dateLastActive":"2023-10-06T20:12:36+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-06T22:08:44+00:00","dateAnswered":"2023-10-06T19:47:59+00:00","acceptedAnswers":[{"commentID":398978,"body":"

Try: =IF(OR(ISBLANK(Title@row), Title@row = \"Resource\"), 0, 3)<\/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":111329,"type":"question","name":"IF Statements and Summing Children","excerpt":"Hi there - I need a hand with a formula. I'm trying to do the following: Show the difference between Budgeted Amount and Quote Amount unless there is a value in the Actual Amount column, then I'd like to show the difference between the Budgeted Amount and the Actual Amount. But I only want to calculate those values if the…","snippet":"Hi there - I need a hand with a formula. I'm trying to do the following: Show the difference between Budgeted Amount and Quote Amount unless there is a value in the Actual Amount…","categoryID":322,"dateInserted":"2023-10-06T16:41:19+00:00","dateUpdated":null,"dateLastComment":"2023-10-06T19:01:55+00:00","insertUserID":159342,"insertUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":159342,"lastUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":32,"score":null,"hot":3393230594,"url":"https:\/\/community.smartsheet.com\/discussion\/111329\/if-statements-and-summing-children","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111329\/if-statements-and-summing-children","format":"Rich","lastPost":{"discussionID":111329,"commentID":398969,"name":"Re: IF Statements and Summing Children","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398969#Comment_398969","dateInserted":"2023-10-06T19:01:55+00:00","insertUserID":159342,"insertUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+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\/8PZPFEHHKZ5S\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-06T19:01:13+00:00","dateAnswered":"2023-10-06T18:49:07+00:00","acceptedAnswers":[{"commentID":398967,"body":"

@Beth Fantozzi 1<\/a> <\/p>

I got you - use this:<\/p>

=IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IF(AND(NOT(ISNUMBER([Quote Amount]@row)), NOT(ISNUMBER([Actual Amount]@row))), 0, IF(ISNUMBER([Actual Amount]@row), [Budgeted Amount]@row - [Actual Amount]@row, [Budgeted Amount]@row - [Quote Amount]@row)))<\/p><\/div><\/div>

It ends up looking like this:<\/p>

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

<\/p>


<\/p>


<\/p>


<\/p>


<\/p>


<\/p>


<\/p>


<\/p>


<\/p>


<\/p>

_____________________________________________________________________________________________<\/p>

👨🏼‍💻 Dan Palenchar | <\/strong>School of Sheets Solutions Consulting<\/strong><\/a> | Smartsheet Aligned Gold Partner<\/strong><\/p>

If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and\/or ❤️Awesome).<\/em><\/p>

🆘 Smartsheet Consulting Inquiries: schoolofsheets.com\/workwithus<\/a><\/p>

▶️ Smartsheet Tutorial Videos: schoolofsheets.com\/youtube<\/a><\/p>

PS - If you have a follow up response attention use @Dan Palenchar<\/a> so I get notified of your reply!<\/strong><\/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

  • Why is a status bar not showing up on all rows?

    I have a column where I have a symbol of a status bar, and i have a formula written based on a "percent complete" column. The status bar shows on most rows, but on random rows it doesnt show at all. Anyone have an idea how to fix this?

  • If or Isblank Formula

    I am trying to write this formula, if the cell is blank return value of 0, OR if the cell contains the word resource return value of 0, otherwise return the value of 3 for both. I have written this formula 500 times today and cannot get it right, can someone help? =IF(OR(ISBLANK(Title@row), 0, 3), (Title@row = "Resource"),…

  • IF Statements and Summing Children

    Hi there - I need a hand with a formula. I'm trying to do the following: Show the difference between Budgeted Amount and Quote Amount unless there is a value in the Actual Amount column, then I'd like to show the difference between the Budgeted Amount and the Actual Amount. But I only want to calculate those values if the…