Can you have two "OR" functions within a COUNTIFS formula?

Devin C.

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.

Tasks.jpg

jessica.smith

@Devin C.

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"))

Devin C.

@jessica.smith ,

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?

jessica.smith

@Devin C.

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"))

Devin C.

@jessica.smith ,

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"))

jessica.smith

@Devin C.

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))

Devin C.

@jessica.smith

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. .