OR in COUNTIFS Formula

=COUNTIFS({part 1 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair"))

Eric Law

@John- Michael Diedrich

Hello John, you need to update to be the following

=COUNTIFS({part 1 #}, [Part Number]@row, {status}, OR(= "Beyond Repair",= "Repaired" ))

https://community.smartsheet.com/discussion/comment/381300#Comment_381300

Thank you Carson, this fixed it. Now I am trying to add 2 more COUNTIFS because I have two other similar columns to count, can you help me figure out why this is coming out #UNPARSEABLE?

=COUNTIFS({part 1 #}, [Part Number]@row, {status}, OR({status} = "Repaired", {status} = "Beyond Repair"), {rma closed date}, <=Date1), COUNTIFS({part 2 #}, [Part Number]@row, {status}, OR({status} = "Repaired", {status} = "Beyond Repair"), {rma closed date}, <=Date1)

Is Date1 a cell in the same sheet, or a cross sheet reference? It will need to be either [Date1]@row or {Date1}.

Making a couple of assumptions:

=COUNTIFS({part 1 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair"), {rma closed date}, <= [Date1]@row)

=COUNTIFS({part 2 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair"), {rma closed date}, <= [Date1]@row)

https://community.smartsheet.com/discussion/comment/381306#Comment_381306

It is in the same sheet but there is only 1 date cell that we use. We change the date cell to see certain windows.

Here is the formula I have that works, but now I need to add two more for the other two columns so all criteria is accounted for:

=COUNTIFS({part 1 #}, [Part Number]@row, {status}, OR({status} = "Repaired", {status} = "Beyond Repair (BER)"), {rma closed date}, <=Date@row)

It changes things If you are always referencing the same Date cell.

=COUNTIFS({part 1 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair"), {rma closed date}, <= $[Date1]$1)

=COUNTIFS({part 2 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair"), {rma closed date}, <= $[Date1]$1)

Using @cell will tell the formula to always refence the Date cell of the current row. It sound like you want the first cell of the Date column to always be referenced.

Switch the ranges inside of the OR function(s) to @cell references instead of cross sheet references.

=COUNTIFS({part 1 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair (BER)"), {rma closed date}, <=Date@row)

https://community.smartsheet.com/discussion/comment/381314#Comment_381314

Thank you Carson, that works. Now, I need to figure out how to add those formulas together so they can account for all 3 part numbers columns. Can you please help me figure out how to do that?

You will need to literally "add" the COUNTIFS together.

=COUNTIFS(........) + COUNTIFS(............)

And you don't need to lock in the cell reference just because you are referencing the same date cell. That is only applicable if you are drag-filling the formula into other cells.

=COUNTIFS({part 2 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair"), {rma closed date}, <= $[Date1]$1) <-- (don't need the $ symbols unless dragfilling)

This formula is coming back as unparseable because of the syntax you are using the add the two together.

should be

=COUNTIFS({part 1 #}, [Part Number]@row, {status}, OR({status} = "Repaired", {status} = "Beyond Repair"), {rma closed date}, <=Date1) + COUNTIFS({part 2 #}, [Part Number]@row, {status}, OR({status} = "Repaired", {status} = "Beyond Repair"), {rma closed date}, <=Date1)

And even the above will not work as expected until you do the "@cell" changes I previously mentioned.

https://community.smartsheet.com/discussion/comment/381315#Comment_381315

Thanks Paul. This confuses me because I am referencing another sheet so I don't think the @cell is needed on this, but I will let you confirm. Just wanted to point that out.

Remember that the syntax for the COUNTIFS function is COUNTIFS(range 1, logical statement 1, range 2, logical statement 2, etc etc etc.

{status}, OR(@cell = "Repaired", @cell = "Beyond Repair (BER)")

Here, {status} is the range, and the entire OR statement is the logical statement. @cell is referencing back to {status} in the range.

Yes. You still need the "@cell" even when referencing another sheet because you have already established the range.

https://community.smartsheet.com/discussion/comment/381324#Comment_381324

https://community.smartsheet.com/discussion/comment/381326#Comment_381326

Thank you both so much for the speedy help. I was able to get this done in no time thanks to you, and now have a better understanding of these formulas.

Final formula:

=COUNTIFS({part 1 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair"), {rma closed date}, <=Date1) + COUNTIFS({part 2 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair"), {rma closed date}, <=Date1) + COUNTIFS({part 3 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair"), {rma closed date}, <=Date1)

One last question, I am unable to make this a column formula. Any idea why? I am guessing because of the date but not 100% sure.

I believe you will need to change all of your references to Date1 to $[Date]$1.