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

Michelle Aristizabal

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

Nic Larsen

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

Nic Larsen

Try it in this format:

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

Michelle Aristizabal

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

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

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

Michelle Aristizabal

That second one works perfectly! Thank you so much!