Checking a box using an IF/OR statement

deb_63_hydracor

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!

Paul Newcome

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

deb_63_hydracor

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.

Paul Newcome

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)

deb_63_hydracor

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!

Paul Newcome

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.

deb_63_hydracor

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

Paul Newcome

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

deb_63_hydracor

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!

Paul Newcome

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.

deb_63_hydracor

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!

deb_63_hydracor

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

Paul Newcome

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