COUNTIF or COUNTIFS
I think I have formulated myself into a corner. What I am attempting to do is create a total number of all open projects based on the multiple status options in 5 separate columns. So, if the data in columns Stats1:Stats5 match X or Y or X, return that total count.
I am able to do this for One status... =COUNTIF([Status1]:[Status5], "Complete") . I need it to look for multiple statuses. And although this works for the one status, it appears to be looking at the other columns in between Status1:Status5. Which is not what I intended. I Only want to be looking at columns Status1, Status2, etc.
1) How do I write this to Only look in specific columns; 2) How do I write this to look for the multiple status options?
Any help would be greatly appreciated.
Comments
-
L_123 ✭✭✭✭✭✭Your issue is your sheet design. Instead of putting status2 to the right, you should put it under status 1, and use the same row. The fewer columns you use the better.
That said if you really want to keep the design your going to have to add each countif and do one for each status.
=COUNTIF([Status1]:[Status1], "Complete")+COUNTIF([Status2]:[Status2], "Complete") etc
-
L_123 ✭✭✭✭✭✭
mark that. Found a way to do it.
=COUNT(COLLECT([Status1]:[Status5],[Status1]:[Status5] ,@cell= "Complete"))
Still this isn't really optimal as it calculates on a bunch of cells it doesn't need to, and if another cell says complete anywhere in that range it will count it in the formula.
-
Rofonic27 ✭✭
Thanks for the response. I'll try that out.
It would seem the design is off, but column Status 2 is not the same as the criteria for the Statuses. It is a second column that refers to Statuses in a separate sheet. This is a master roll up sheet used to feed a dashboard. So Status1:Status5 represent the status columns of sheets 1-5.
And I guess I should clarify, I am not looking to just collect Status Complete. I am looking to collect Complete, In Progress, On Hold, In Testing (Etc) from Status1:Status5 in a single count. So how many of those matching criteria are in Columns Status1:Status5.
-
Paul Newcome ✭✭✭✭✭✭=COUNTIF([Status1]:[Status5], OR(@cell= "Complete",@cell= "In Progress",@cell= "On Hold", etc...))
Similar to Luke's but skips out on the COLLECT part and combines all of the various dropdown options. If you didn't want to include the other columns in between the statuses you would have to break it down to
=COUNTIF([Status1]:[Status1], OR(@cell= "Complete",@cell= "In Progress",@cell= "On Hold", etc...)) +
COUNTIF([Status2]:[Status2], OR(@cell= "Complete",@cell= "In Progress",@cell= "On Hold", etc...)) + etc...
thinkspi.com
-
Rofonic27 ✭✭
Got it. Thanks to both of you.
Help Article Resources
Categories
Like that it is working partially, because as I have sub assemblies, I will need to take the quantity of the PARENT() and multiply it to have the correct value.<\/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":106651,"type":"question","name":"Incorrect Argument Set with nested IF","excerpt":"Counting number of occurances where month\/year is 1\/23 AND where cell within the row does not contain \"Color Add\" =COUNTIFS({New Product Status (Imagen) Go Live Launch Date}, IFERROR(MONTH(@cell), 0) = 5, {New Product Status (Imagen) Go Live Launch Date}, IFERROR(YEAR(@cell), 0) = 2023, {New Product Status (Imagen) new or…","categoryID":322,"dateInserted":"2023-06-20T18:01:37+00:00","dateUpdated":"2023-06-20T19:20:48+00:00","dateLastComment":"2023-06-21T13:22:46+00:00","insertUserID":74386,"insertUser":{"userID":74386,"name":"Stephanie Kiecker","url":"https:\/\/community.smartsheet.com\/profile\/Stephanie%20Kiecker","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!W2nUX5neWjw!6djX-omKb7Q!x0GPDTlMr6q","dateLastActive":"2023-06-21T13:31:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":74386,"lastUserID":150413,"lastUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-21T13:21:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":37,"score":null,"hot":3374639663,"url":"https:\/\/community.smartsheet.com\/discussion\/106651\/incorrect-argument-set-with-nested-if","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106651\/incorrect-argument-set-with-nested-if","format":"Rich","lastPost":{"discussionID":106651,"commentID":381471,"name":"Re: Incorrect Argument Set with nested IF","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381471#Comment_381471","dateInserted":"2023-06-21T13:22:46+00:00","insertUserID":150413,"insertUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-21T13:21:43+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-06-21T11:33:23+00:00","dateAnswered":"2023-06-20T20:32:28+00:00","acceptedAnswers":[{"commentID":381387,"body":" The @cell part at the end is wrong. It will need to be @cell = \"Color Add\", but in doing that, you will need to adjust your NOT(CONTAINS part. Try replacing it with NOT(@cell = \"Color Add\")<\/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":106656,"type":"question","name":"Incorrect Argument - Second set of eyes please","excerpt":"I'm not sure what I'm missing but would appreciate some help with the below formula it is giving me an incorrect argument set and I can't figure out why. =COUNTIFS({Greet}, OR(@cell = \"yes\", @cell = \"NA\"), {Week Beginning}, [Report Date]@row, {Monitor Type}, \"Monitor\") \/ COUNTIFS({Greet}, OR(@cell = \"yes\", @cell = \"no\",…","categoryID":322,"dateInserted":"2023-06-20T18:34:48+00:00","dateUpdated":null,"dateLastComment":"2023-06-21T12:22:01+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-21T12:10:09+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-21T16:49:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3374638009,"url":"https:\/\/community.smartsheet.com\/discussion\/106656\/incorrect-argument-second-set-of-eyes-please","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106656\/incorrect-argument-second-set-of-eyes-please","format":"Rich","lastPost":{"discussionID":106656,"commentID":381461,"name":"Re: Incorrect Argument - Second set of eyes please","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381461#Comment_381461","dateInserted":"2023-06-21T12:22:01+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-21T16:49:20+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-06-21T12:12:06+00:00","dateAnswered":"2023-06-21T12:01:53+00:00","acceptedAnswers":[{"commentID":381446,"body":" Syntax is right, so the other thing that can cause that error is range sizes not matching. Double check your cross sheet references to ensure they are all the same size and shape.<\/p> I have trouble sometimes with this when I am referencing a large sheet or my internet is behaving poorly. Sometimes it takes a few extra seconds for the sheet to fully load in when you are selecting the range for the cross sheet reference. When the sheet does fully load in, the selection will always revert to the \"home cell\" or the cell in the top right corner. Even if you have already clicked on a column header. Then I have to go back and edit the reference and pause for a few seconds to let the sheet load into the window. You should be good to go when the sheet name appears near the top left of the window.<\/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":[]}">
=SUMIF([Part number]$185:[Part number]$369, CONTAINS([Part number]167, @cell), Quantity$185:Quantity$369)<\/code><\/strong><\/p>
<\/p>
