Can you have two "OR" functions within a COUNTIFS formula?
I am trying to count a column based on data that is in two other columns. Each of those columns could have different information so I am trying to use a OR function but I am running into a couple roadblocks.
Scenario: I want to count the row in column A if it equals "IT" and if column B equals "Apple or "Orange" and if column C equals "In Transit" or Delivered. (See attachment pic)
This works if I only do the OR function on Column C and have column B be one specific value
=COUNTIFS(A:A, "IT", B:B, "Apple", C:C, OR(@cell = "In Transit", @cell = "Delivered"))
This works how I want it to in excel but it looks like braces are not an acceptable operator. I think I am close with this one.
=SUM(COUNTIFS(A:A, "IT", B:B, {"Apple","Orange"}, C:C, {"Delivered";"In Transit"}))
I tried using two OR functions in one countifs formula but get a #unparseable error that I can troubleshoot out.
=COUNTIFS(A:A, "IT", B:B, OR(@cell = "Apple", @cell = "Orange"), C:C OR(@cell = "In Transit", @cell = "Delivered")))
Any thoughts? I feel like I am close.
Answers
-
I think you might just be missing a comma between your C:C range and range criteria. Does this work for you:
=COUNTIFS(A:A, "IT", B:B, OR(@cell = "Apple", @cell = "Orange"), C:C, OR(@cell = "In Transit", @cell = "Delivered"))
-
That's it! It's always the missing comma. The formula looks to be working just not how I expected.
I wanted it to be counted if EITHER column B or C matched.
Example: If Column B = Apple but column C didn't match either Transit or Delivered it would still be counted.
What I am seeing is if Column B is blank or anything other then Apple or Orange and Column C matches it will not be counted. Thoughts?
-
How about
=SUM(COUNTIFS(A:A, "IT", B:B, OR(@cell = "Apple", @cell = "Orange")), COUNTIFS(A:A, "IT", C:C, OR(@cell = "In Transit", @cell = "Delivered"))) - COUNTIFS(A:A, "IT", B:B, OR(@cell = "Apple", @cell = "Orange"), C:C, OR(@cell = "In Transit", @cell = "Delivered"))
-
Very impressive formula. I am not sure how that works but it does perfectly in my testing formula modal. (nicely done). When I take that same modal and change the column names and @cell information to my production formula it works 2/3 of the way. If the "IT" column has a yes it works. If "Approval Status" meets the correct parameters it works. But if "Aprroval Status" is blank or has a different entry and "Project Status" meets the correct parameters is doesn't get counted. I poured over your new formula and and they seem to match. I tried several different changes but keep getting the same result. I am not sure what the difference would be. (If it's another comma i quit). Thoughts?
=SUM(COUNTIFS(IT:IT, "Yes", [Approval Status]:[Approval Status], OR(@cell = "A", @cell = "B", @cell = "C", @cell = "D", @cell = "E")), COUNTIFS(IT:IT, "Yes", [Project Status]:[Project Status], OR(@cell = "1", @cell = "2", @cell = "3"))) - COUNTIFS(IT:IT, "Yes", [Approval Status]:[Approval Status], OR(@cell = "A", @cell = "B", @cell = "C", @cell = "D", @cell = "E"), [Project Status]:[Project Status], OR(@cell = "1", @cell = "2", @cell = "3"))
-
Try taking the Project Status values out of quotes (e.g., @cell=1 instead of @cell="1"). I think Smartsheet is having a hard time with string vs value in this particular formula
=SUM(COUNTIFS(IT:IT, "Yes", [Approval Status]:[Approval Status], OR(@cell = "A", @cell = "B", @cell = "C", @cell = "D", @cell = "E")), COUNTIFS(IT:IT, "Yes", [Project Status]:[Project Status], OR(@cell = 1, @cell = 2, @cell = 3))) - COUNTIFS(IT:IT, "Yes", [Approval Status]:[Approval Status], OR(@cell = "A", @cell = "B", @cell = "C", @cell = "D", @cell = "E"), [Project Status]:[Project Status], OR(@cell = 1, @cell = 2, @cell = 3))
-
Thank you very much. Removing the commas did the trick. I was able to replace the sample data in the string with real data and with a lot of regression testing everything is working. That is a very complicated formula in my opinion. Thank you for working through it with me. I appriciate it. .
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!