How do I fix error #invalid operation when trying to use IF formula on multiple rows?

The formula works when I only include 1 row. But once I try to drag the blue box to include multiple rows in the formula, it gives me this error:

I'm trying to add a formula that if all of the checkboxes within a range of rows are checked, that the top row reflects "APPROVED" and if any of unchecked within the range, that it reflects "UNAPPROVED".

Any help you can provide, would be great! Thanks

Best Answer

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    This worked in testing, although not sure it's any better than adjusting the count:

    =IF(COUNTM([ASV Scans Approved and Updated]3:[ASV Scans Approved and Updated]25) = COUNTIF([ASV Scans Approved and Updated]3:[ASV Scans Approved and Updated]25, 1), "Approved", "Unapproved")

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Try it in this format:

    =IF(COUNTIF([ASV Scans Approved and Updated]:[ASV Scans Approved and Updated], 1) >= 1, "Approved", "Unapproved")

  • That got rid of my error, thank you!

    But now how do I get it so that the "Approved" is only showing if all of the cells within the range are checked or 1? Basically all of the rows need to be checked off in order for the Approved to show up. If any of the cells in the range of rows 3-25 are unchecked or 0 then the value will not be Approved but it would show as Unapproved.

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Since you are using a defined range, the easiest way would just adjust the ">=1" to 22 so the count has to match the number of checked boxes you are looking for.

    I'll have to think on if there's a simpler method to evaluate the range and look for a 100% and then return a value.

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    This worked in testing, although not sure it's any better than adjusting the count:

    =IF(COUNTM([ASV Scans Approved and Updated]3:[ASV Scans Approved and Updated]25) = COUNTIF([ASV Scans Approved and Updated]3:[ASV Scans Approved and Updated]25, 1), "Approved", "Unapproved")

  • That second one works perfectly! Thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!