Countifs trouble
jacquedale
✭✭✭✭
I have done this before but for the life of me I can't get it to work this time. I simply need to "count if" the "Contractor" is "X" AND "Status" is "Open" = qty
I tried =COUNTIF(range1, criteria1) + COUNTIF(range2, criteria2)
*and many, many more.
No matter what formula I try it won't work.
Answers
-
Paul Newcome ✭✭✭✭✭✭You need a COUNTIFS.
=COUNTIFS(Contractor:Contractor, @cell = "X", Status:Status, @cell = "Open")
-
jacquedale ✭✭✭✭
That isn't working either. That gives me a count of "0" even when there is "1"
-
jacquedale ✭✭✭✭
Got it! I was doing it backwards. Once I changed it to "open" as the first criteria and then the contractor it worked!. Thank you for your help@Paul Newcome
=COUNTIFS({QAR KPI Data Range 4}, "open", {QAR KPI Data Range 3}, labels@row)
Help Article Resources
Categories
Want to practice working with formulas directly in Smartsheet?
Check out theFormula Handbook template!
Check out theFormula Handbook template!
Hi @SadieW<\/a> <\/p>
<\/p>
Here are the formulas that you can try<\/p>
- Count of starts for an individual =COUNTIFS(Priority:Priroty, 1, CA:CA, \"Full name as it appears in the cell\")<\/li>
- Sum of contract value for an individual =SUMIFS([Contract value column]:[Contract value column], Priority:Priroty, 1, CA:CA, \"Full name as it appears in the cell\")<\/li><\/ol>"}]}},"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":112244,"type":"question","name":"Countifs formula","excerpt":"Hi Team reaching out for some help again, i need a countifs formula to count all the high priority actions (Priority column) that are currently either not started or ongoing (Status column) Thanks in advance","snippet":"Hi Team reaching out for some help again, i need a countifs formula to count all the high priority actions (Priority column) that are currently either not started or ongoing…","categoryID":322,"dateInserted":"2023-10-27T02:53:17+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T04:03:23+00:00","insertUserID":158264,"insertUser":{"userID":158264,"name":"Hobo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Hobo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZLW3ELEW8RGX\/nZR392EQUH89I.jpg","dateLastActive":"2023-10-27T08:27:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":158264,"lastUser":{"userID":158264,"name":"Hobo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Hobo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZLW3ELEW8RGX\/nZR392EQUH89I.jpg","dateLastActive":"2023-10-27T08:27:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":16,"score":null,"hot":3396755800,"url":"https:\/\/community.smartsheet.com\/discussion\/112244\/countifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112244\/countifs-formula","format":"Rich","lastPost":{"discussionID":112244,"commentID":401963,"name":"Re: Countifs formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401963#Comment_401963","dateInserted":"2023-10-27T04:03:23+00:00","insertUserID":158264,"insertUser":{"userID":158264,"name":"Hobo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Hobo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZLW3ELEW8RGX\/nZR392EQUH89I.jpg","dateLastActive":"2023-10-27T08:27:18+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\/MLV6R3XY3ELA\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/MLV6R3XY3ELA\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/MLV6R3XY3ELA\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/MLV6R3XY3ELA\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/MLV6R3XY3ELA\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/MLV6R3XY3ELA\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-27T09:32:13+00:00","dateAnswered":"2023-10-27T03:45:21+00:00","acceptedAnswers":[{"commentID":401961,"body":"
