Checking a box using an IF/OR statement
Hi,
I am trying to check a box if either of these statements are true, but I'm getting an Invalid Data Type error. The "Operational" statuses are from a drop down.
Data Upload is "Operational"
OR
Retail Survey is "Operational"
This is what I have:
=IF(OR([Data Upload Status]:[Data Upload Status], ="Operational", [Retail Survey Launch Status]:[Retail Survey Launch Status], ="Operational"), 1)
Thanks!
Answers

Are you looking for any rows where this is true, or is this formula to be applied to every row individually?

Not sure I get the difference between the two. I would like every row checked that has either of the two statuses as Operational. I want it to apply to all rows, including those added later.

I meant to ask if you were wanting to check a single box (such as in a sheet summary field) if at least one row on the sheet matched of if you were wanting to check multiple individual boxes on a row by row basis. It sounds like the second option, so try this:
=IF(OR([Data Upload Status]@row = "Operational", [Retail Survey Launch Status]@row = "Operational"), 1)

Thanks. That formula worked. This formula is for a helper column that you suggested that I create in your response to me on 6/21 (Because it is two different ranges, my suggestion would be to insert a hidden helper column on the source sheet and use a basic IF/OR formula to check a box on each row if either of them are checked and then reference this helper column in the COUNTIFS.).
Now, I need to combine the formula you provided above with the following conditions, using AND (countif the box is checked AND...
I tried it with just one of the Experiencia Rounding Statuses above and it doesn't work so I didn't didn't attempt to go any further:
=COUNTIFS(AND({Data Upload or Retail Survey Operational}, 1, IF(AND({Exp Rounding Status}@row<>"Operational"))).
I can leave out the [Monthly DataCor Revenue] is Blank condition if there's not a way to incorporate it but then I would want to deduct 34 (or whatever the current amount is) from the result the formula brings.
Thanks!

How many different statuses do you have in total? I see you are excluding 5. If there are only 7 and you want to count two of them, then it would be much easier to do that than it would be to exclude 5.

There are a lot!! :( That's part of the reason I'm having such a hard time with formulas. Nothing is black and white!

No worries. We can exclude all. I just wanted to confirm which way would be easier. You are going to want to follow this kind of syntax:
=COUNTIFS({Data Upload or Retail Survey Operational}, @cell = 1, {Exp Rounding Status}, AND(@cell <> "First Exclusion", @cell <> "Second Exclusion", ...................), {Monthly DataCor Revenue}, @cell = "")

Thanks Paul. Your formula works without giving me an error, however it is not giving me the correct count and I can't seem to figure out why. Do you have any suggestions?
Thanks!

Can you post the exact formula you are using, and are you able to provide a screenshot of a portion of the data that shows an example of each selection? How far off is the number?
You can also try applying a filter to the sheet to mimic the conditions in the formula to see what that comes up with. That will help us figure out if there is a data issue or a formula issue.

Above is a screen shot of the filter being used on a report. It has a couple of extra conditions not in "our" formula but they only change the count by 1 or 2.
This is the formula I'm using. It's giving me a count of 79, but count should be 234. I'm thinking that we're missing an "OR" somewhere in the formula??
=COUNTIFS({Data Upload or Retail Survey Operational}, @cell = 1, {Exp Rounding Status}, AND(@cell <> "Operational", @cell <> "Needs Post Launch Review", @cell <> "Post Launch Review Scheduled", @cell <> "Adding a Service", @cell <> "Requested Cancellation"), {Monthly DataCor Revenue}, @cell = "")
Thanks!

I forgot to add this:
This part of the formula takes care of the conditions on the screenshot to check off a box if either Data Upload is checked OR Retail survey is checked. You had suggested creating a helper column which I did ("Data Upload or Retail Survey Operational")
=COUNTIFS({Data Upload or Retail Survey Operational}, @cell = 1,.....

Try adjusting the filter to incorporate the checkbox. We want it as close to the formula as we can get it.
I also notice that you only have 5 "not equal to" arguments in your formula, but your filter is listing 6. You also don't have anythign in the formula about a box being unchecked.
=COUNTIFS({Data Upload or Retail Survey Operational}, @cell = 1, {Exp Rounding Status}, AND(@cell <> "Operational", @cell <> "Needs Post Launch Review", @cell <> "Post Launch Review Scheduled", @cell <> "Adding a Service", @cell <> "Requested Cancellation"), {Monthly DataCor Revenue}, @cell = "")
Help Article Resources
Categories
Check out the Formula Handbook template!