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"

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!

Find more posts tagged with

Formulas

Accepted answers

All comments

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

Screen Shot 2022-06-27 at 1.39.08 PM.png

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

Screen Shot 2022-06-30 at 12.32.02 PM.png

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.