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
-
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
-
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.
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!