COUNTIFS Using And as well as Or
Comments
-
Paul Newcome ✭✭✭✭✭✭@Joe HaneyTry this...
=IF(COUNTIFS([Study Setup]2:[Third Party Labs]2, "In Progress") + COUNTIFS([Study Setup]2:[Third Party Labs]2, "Completed") >= 3, "Green", IF(COUNTIFS([Study Setup]2:[Third Party Labs]2, "In Progress") + COUNTIFS([Study Setup]2:[Third Party Labs]2, "Completed") = 2, "Yellow", "Red"))
The parenthesis were not the only issue though. It looks like you have "smart quotes" around the word "Yellow". Notice the slanting and formatting that shows open vs closed quotes as opposed to the straight up and down quotes everywhere else? Those are smart quotes and can't be used in formulas in Smartsheet. I went ahead and replaced those in the above as well.
-
Alicia Jones ✭✭
Hi!
I am trying to reference another sheet and collect all statuses that are completed within a date range, I am trying to use countifs but it is not working:
=COUNTIFS(({Status}, @cell = "Complete", AND({End Date}, @cell >= Start1, {End Date}, @cell <= Finish1))
Any advice would be appreciated!
-
Alicia Jones ✭✭
I actually figured it out: =COUNTIFS({Status}, @cell = "Complete", {End Date}, @cell >= Start1, {End Date}, @cell <= Finish1)
-
I am having an issue with my formula:
=COUNTIFS([Start Date]:[Start Date], IFERROR(MONTH(@cell), 0) >= 7, Type:Type, OR(@cell = "Residence Life Event", @cell = "6 in first 6", @cell = "Welcome Back Event", [P.A.W.S (Res Life Only)]:[P.A.W.S (Res Life Only)], "Philanthropic"))
I want it to count events that are equal to or greater than a start date of July (7), if the Type is selected as either a Residence Life Event, 6 in first 6, or Welcome Back Event, and if the column P.A.W.S (Res Life Only) is selected as Philanthropic.
-
Paul Newcome ✭✭✭✭✭✭
@Alexia SaphosIt is simply a case of a misplaced parenthesis. One needs moved from the end of the formula to close out the OR statement.
=COUNTIFS([Start Date]:[Start Date], IFERROR(MONTH(@cell), 0) >= 7, Type:Type, OR(@cell = "Residence Life Event", @cell = "6 in first 6", @cell = "Welcome Back Event"), [P.A.W.S (Res Life Only)]:[P.A.W.S (Res Life Only)], "Philanthropic")
-
Thank you,@Paul Newcome. I added the parenthesis and am still getting #UNPARSEABLE
=COUNTIFS([Start Date]:[Start Date], IFERROR(MONTH(@cell), 0) >= 7, Type:Type, OR(@cell = "Residence Life Event", @cell = "6 in first 6", @cell = "Welcome Back Event"), [P.A.W.S (Res Life Only)]:[P.A.W.S (Res Life Only)], "Philanthropic"))
-
Paul Newcome ✭✭✭✭✭✭
That's because you didn't want to ADD a parenthesis. You wanted to MOVE a parenthesis. Take a look at the formula I provided. There should only be one at the end.
-
Sorry all fixed my issue and can not delete my comment - ignore me :)
-
@Paul NewcomeHello Paul, I was wondering if you would be able to assist, I have been reviewing this thread to see what formula best fits my smartsheet. I am wanting to reference a second sheet and star the what is found. In essence, I would like to set it up as: If the employee ID is found on sheet 1 and on sheet 2 mark the star if it is not found don't mark the star. I am just not sure how to tie this statement in an AND statement to reference the second sheet. I was able to do the first step and it worked....I just don't know how to tie in the second sheet . This is what I have so far...
=IF(COUNTIFS({Employee ID 1}, [Employee ID]@row) > 0, 1, 0)
Any help from anyone is greatly appreciated!
-
Paul Newcome ✭✭✭✭✭✭
@Ruby MunozI'm not sure I follow. Are you working with 3 sheets total? If the employee id is found on sheets 1 and 2, then highlight the start on sheet 3?
-
@Paul NewcomeHi Paul, thank you for responding. Yes, thats correct. I am working on 3 sheets.
-
Paul Newcome ✭✭✭✭✭✭
@Ruby MunozOk. So if you need to to be on BOTH sheets, you would use two separate COUNTIFS along with an AND.
=IF(AND(COUNTIFS({Sheet 1}, [Employee ID]@row) > 0, COUNTIFS({Sheet 2}, [Employee ID]@row) > 0), 1)
-
Erin Ballantine ✭✭✭
Can you use Countif with an immediate Or statement? In other words, if I wanted to count every item that had a [Finish Date] of mm/dd/yyyy OR a [Complete Date] of mm/dd/yyyy?
-
Paul Newcome ✭✭✭✭✭✭
@Erin BallantineYou would need to write two separate COUNTIFS statements (one for each column) and then add them together.
=COUNTIFS([Finish Date]:[Finish Date], @cell = DATE(2021, 01, 01)) + COUNTIFS([Complete Date]:[Complete Date], @cell = DATE(2021, 01, 01))
-
shuckel ✭
我想创建一个公式,会告诉我hen something for the same row has something specific in two separate columns. I can't figure out what the formula should be... I've tried the formula below but I have two problems: (1) it comes back "#unparseable" and (2) I'm not specifying that I need these things to be true for the same row.
In the picture, I've provided some dummy info to explain what I'm looking for. Using this, how can I pull the total number of rows where the ColumnA = "A" and ColumnB = "identify" for the same row? (The answer in this screenshot is 2.)
Here's what I tried:
=COUNTIF([ColumnA]:[ColumnA], "A") AND ([ColumnB]:[ColumnB], "identify")
Help Article Resources
Categories
Check out theFormula Handbook template!
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109490,"type":"question","name":"HAS exact match within multiselect - Numbered Values","excerpt":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…","snippet":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains)))…","categoryID":322,"dateInserted":"2023-08-25T19:26:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":14,"score":null,"hot":3386003780,"url":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","format":"Rich","lastPost":{"discussionID":109490,"commentID":392694,"name":"Re: HAS exact match within multiselect - Numbered Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392694#Comment_392694","dateInserted":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+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\/KJPRLKL2FW16\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:49:35+00:00","dateAnswered":"2023-08-25T23:58:23+00:00","acceptedAnswers":[{"commentID":392688,"body":"
Hi, <\/p>
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T00:32:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":20,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T00:32:09+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-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
