How would I add a MAX function to COUNTIFS formula?

ClanMcDe

ISSUE 1:

I have a formula I'm using to update a radio button called "Paused" to identify items in another sheet that are paused. I need to make sure it looks at the most recent occurrence of the item in the list using the last run date.

How would I add a MAX function to this formula using the last run date.

=COUNTIFS({Analyst Request Tracker with Form Range 2}, [Task Name]@row, {Periscope Request Tracker Range 1}, <>1)

ISSUE 2:

how do I keep from having "#BOOLEAN EXPECTED" error when the radio button is not checked?

Paul Newcome

I may be misunderstanding what you are trying to do, but what about wrapping the COUTNIFS in an IF to say that IF the COUNTIFS is greater than zero, check the box?

=IF(COUNTIFS(…………….) > 0, 1)

From the sound of it, your COUNTIFS is at times outputting the number two (or more). The problem is, it is residing in a checkbox column. Checkbox columns require boolean inputs (1 or 0 in this instance). It isn't breaking when there is only one because 1 is an acceptable input for a checkbox column.

Using the IF statement to only output a 1 should help resolve this issue.

Paul Newcome

Are you able to provide screenshots for context?

ClanMcDe

https://community.smartsheet.com/discussion/comment/451445#Comment_451445

Hi Paul,

I think this is from your 2 sheet solution for recurring tasks.

If the schedule sheet has 2 or more items for the same task I get the #BOOLEANEXPECTED error. do you know how to solve this?

the exact formula being used is:

=COUNTIFS({Periscope Request Tracker Range 1}, [Task Name]@row, {Analyst Request Tracker with Form Range 3}, <>1)

in the automation we added 2nd logic stream that will create the next task when we hit the run date even if the current task was not completed yet. which because it can result in 2 tasks in the schedule sheet it is giving the boolean error.

Thanks in advance.

Paul Newcome

I may be misunderstanding what you are trying to do, but what about wrapping the COUTNIFS in an IF to say that IF the COUNTIFS is greater than zero, check the box?

=IF(COUNTIFS(…………….) > 0, 1)

From the sound of it, your COUNTIFS is at times outputting the number two (or more). The problem is, it is residing in a checkbox column. Checkbox columns require boolean inputs (1 or 0 in this instance). It isn't breaking when there is only one because 1 is an acceptable input for a checkbox column.

Using the IF statement to only output a 1 should help resolve this issue.