OR in COUNTIFS Formula
Helllo Community,
I am struggling to figure out this COUNTIFS formula with OR included. I am trying to count the amount of cells on a separate sheet that have the same part number and their status is either "Repaired" or "Beyond Repair". I will paste my current formula below, and it is outputting invalid operation. Any help here is appreciated!
=COUNTIFS({part 1 #}, [Part Number]@row, OR({status} = "Repaired", {status} = "Beyond Repair"))
Answers
-
=COUNTIFS({part 1 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair"))
-
Hello John, you need to update to be the following
=COUNTIFS({part 1 #}, [Part Number]@row, {status}, OR(= "Beyond Repair",= "Repaired" ))
-
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)
-
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)
-
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.
=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)
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.
-
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.
-
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.
-
Column formulas cannot reference a specific row number. All cell references must be "@row" references.
Try putting the date in a Sheet Summary field and referencing that instead or putting the date on every row and using "@row" references.
Date on every row:
COUNTIFS({part 3 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair"), {rma closed date}, <=Date@row)
Date as Sheet Summary Field:
COUNTIFS({part 3 #}, [Part Number]@row, {status}, OR(@cell = "Repaired", @cell = "Beyond Repair"), {rma closed date}, <=Date#)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
