Counts if with multiple conditons
Help!
Trying to get a count if line items that do not have a column marked resolved and have a issue type of Reports Missing.
=COUNTIFS({Aimbridge Report Issues Range 2}, "Reports Missing",{Aimbridge Report Issues Range 2}, 0)
I am sure it is simple...but I an not figuring it out and need help!
Comments
-
Nic Larsen ✭✭✭✭✭✭The formula above looks like both conditions are using the same range. Should the 2nd range be pointed at a different column with a different range name?
-
Paul Newcome ✭✭✭✭✭✭My thoughts exactly. Double check the ranges. There should be 2 different ranges if one is looking for text and another is looking for a checkbox.
Help Article Resources
Categories
Check out theFormula Handbook template!
<\/p>
If you open up the column properties, you should be able to see any extra spaces in the column name there. You can also copy the column name from there and then paste it into your automation.<\/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":446,"urlcode":"update-requests","name":"Update Requests"}]},{"discussionID":109048,"type":"question","name":"Multiple IF statements in the formula","excerpt":"Hi! I want to create a priority matrix using urgency and important. I've used this formula below. There is no error on the formula but the cell only returns P2. I feel I'm doing something wrong with adding multiple IF statements. Please help =IF(Urgency@row = \"NOT URGENT\", IF(Importance@row = \"IMPORTANT\", \"P2\",…","snippet":"Hi! I want to create a priority matrix using urgency and important. I've used this formula below. There is no error on the formula but the cell only returns P2. I feel I'm doing…","categoryID":322,"dateInserted":"2023-08-16T17:17:28+00:00","dateUpdated":null,"dateLastComment":"2023-08-16T19:06:27+00:00","insertUserID":165213,"insertUser":{"userID":165213,"name":"Myra Kodidela","title":"Project and Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Myra%20Kodidela","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-16T20:39:20+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-08-16T23:29:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":42,"score":null,"hot":3384420835,"url":"https:\/\/community.smartsheet.com\/discussion\/109048\/multiple-if-statements-in-the-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109048\/multiple-if-statements-in-the-formula","format":"Rich","lastPost":{"discussionID":109048,"commentID":390975,"name":"Re: Multiple IF statements in the formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390975#Comment_390975","dateInserted":"2023-08-16T19:06:27+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-08-16T23:29:24+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-08-16T18:38:49+00:00","dateAnswered":"2023-08-16T17:45:41+00:00","acceptedAnswers":[{"commentID":390955,"body":"
Thank you, it worked when I used<\/p>
=IF(AND(Urgency@row = \"URGENT\", Importance@row = \"IMPORTANT\"), \"P1\", IF(AND(Urgency@row = \"NOT URGENT\", Importance@row = \"IMPORTANT\"), \"P2\", IF(AND(Urgency@row = \"URGENT\", Importance@row = \"NOT IMPORTANT\"), \"P3\", IF(AND(Urgency@row = \"NOT URGENT\", Importance@row = \"NOT IMPORTANT\"), \"P4\"))))<\/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":109044,"type":"question","name":"IF ERROR","excerpt":"Hi there, I need a hand setting up a formula between two sheets. In sheet 1, I have a list of names and a variety of states. In sheet 2 I have a list of names and a variety of states. I would like a formula in sheet 1 that looks at sheet 2 and returns a checked box of the combination of name and state is not found in sheet…","snippet":"Hi there, I need a hand setting up a formula between two sheets. In sheet 1, I have a list of names and a variety of states. In sheet 2 I have a list of names and a variety of…","categoryID":322,"dateInserted":"2023-08-16T16:57:34+00:00","dateUpdated":null,"dateLastComment":"2023-08-16T19:09:28+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-08-16T18:02:17+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-08-16T23:29:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3384419822,"url":"https:\/\/community.smartsheet.com\/discussion\/109044\/if-error","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109044\/if-error","format":"Rich","lastPost":{"discussionID":109044,"commentID":390980,"name":"Re: IF ERROR","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390980#Comment_390980","dateInserted":"2023-08-16T19:09: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-08-16T23:29:24+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-08-16T18:37:49+00:00","dateAnswered":"2023-08-16T17:19:15+00:00","acceptedAnswers":[{"commentID":390948,"body":"
Try this:<\/p>
=IF(COUNTIFS({Other Sheet Name Column}, @cell = [Name Column]@row, {Other Sheet State Column}, @cell = [State Column]@row) = 0, 1)<\/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":[]}">
