How to determine the last two lines of a sheet when row is checked

cabbsman2026

I'm trying to find and number in the Rank column the last two rows that have a status column with a check in the checkbox. I am using the following formula but am getting a #INVALID OPERATION error.

=IF(AND(Status@row = 1, [Row #]@row >= LARGE(COLLECT([Row #]:[Row #], Status:Status, 1), 2)), 1, 0)

The properties for each column are as follows:

Row # = # Auto number

Rank = Text/Number

Status = Checkbox

Row # Rank Status

141	#INVALID OPERATION	true
142		true
143
144		true

My expectations is that the row 142 will display '1' and row 144 will display '2' in the Rank column as these are the last two rows that have a checkbox in Status column.

Row # Rank Status

141		true
142	1	true
143
144	2	true

Any ideas as to why I am getting the #INVALID OPERATION error?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Give this a try:

=IF(AND([Checkbox Column]@row = 1, [Row ID]@row >= IFERROR(LARGE(COLLECT([Row ID]:[Row ID], [Checkbox Column]:[Checkbox Column], @cell = 1), MIN(COUNTIFS([Checkbox Column]:[Checkbox Column], @cell = 1), 2)), 0)), COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Checkbox Column]:[Checkbox Column], @cell = 1) - (COUNTIFS([Checkbox Column]:[Checkbox Column], @cell = 1) - 2))

All comments

Kerry St. Thomas

AND tends to be …annoying. You're comparing two different column types in their own operation, but AND combines them all into one comparison operation - things go quickly haywire. Basically, Smartsheet's having a sulk. :)

The solution I suggest is to replace the AND with a negative error trap type of logic:
=IF(Status@row=0,0,IF([Row #]@row…

It's not the most elegant thing ever, but… well, there you are.

Paul Newcome

I use the AND function to compare different column/data types frequently without issue.

Are you able to provide a screenshot of the sheet itself? What are the column properties of the auto-number column? In your original post, you mention 4 columns but are only showing three. Are you able to clarify?

cabbsman2026

Hi Paul,

Here's a screen shot of the sheet.

And here's the property of each col:

I appreciate the help.

Mike

Paul Newcome

The prefix for your auto-number column may be causing it to output as text instead of numbers. Try a helper column (that can be hidden after setting up) with a formula of:

=VALUE([Row #]@row)

Then reference this column in your formula instead of the original auto-number column.

cabbsman2026

Hi I have one more question. I'm trying to get display a 1 for the 1st row found that has a checkbox and 2 for the second row with a checkbox. With this formula, I am only getting a '1' in each row found.

=IF(AND([Checkbox Column]@row = 1, [Row ID]@row >= LARGE(COLLECT([Row ID]:[Row ID], [Checkbox Column]:[Checkbox Column], 1), MIN(2, COUNTIF([Checkbox Column]:[Checkbox Column], 1)))), 1, 0)

What I am getting now:

What I am expecting:

Do you have any ideas so I can get a 1 and a 2 for each row found?

cabbsman2026

That did it! Thank you so much.

Paul Newcome

Give this a try:

=IF(AND([Checkbox Column]@row = 1, [Row ID]@row >= LARGE(COLLECT([Row ID]:[Row ID], [Checkbox Column]:[Checkbox Column], @cell = 1), 2)), COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Checkbox Column]:[Checkbox Column], @cell = 1))

cabbsman2026

=IF(AND([Checkbox Column]@row = 1, [Row ID]@row >= LARGE(COLLECT([Row ID]:[Row ID], [Checkbox Column]:[Checkbox Column], 1), MIN(2, COUNTIF([Checkbox Column]:[Checkbox Column], 1)))), 1, 0)

What I am getting now:

What I am expecting:

Do you have any ideas so I can get a 1 and a 2 for each row found?

cabbsman2026

Hi, I tried the formula but get several errors depending which check boxes are selected.

First what does work. When I select two check boxes I get the numbers 1, and 2 under the Rank column.

When I select more than 3 check boxes I lose the number 1 and the newly selected checkbox is number 3.

When I select only one check box, no matter which row, I get the following error:

When I select no checkbox I get the same error:

Paul Newcome

Give this a try:

cabbsman2026

That did it! Tha

Paul Newcome

Happy to help.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.