How do I exclude a cell with checkbox from formula for a specific row?

Judy Jefferson

I'm trying to use the COUNTIF statement to total a range in a row with checkboxes that are selected and provide the percentage of what was checked. That part worked (highlighted in screenshot):

=COUNTIF([Thumbnail Image Rec'd]6:[Migration Complete]6, 1) / COUNT([Thumbnail Image Rec'd]6:[Migration Complete]6)

The issue is I cannot figure out how to exclude a cell within that range when that cell doesn't apply to a specific row

i.e. Row 7's formula does not need "Video File Rec'd" included

What would I need to change in my formula for it to understand I want to exclude a cell (and in some cases, more than one cell)?

Kelly Moore

Hey Judy

As you are looking at the data ask yourself how you are deciding how many columns to use. If you can describe that to me, I may be able to help you with the formula.

If there isn't a way for the formula to decide then I would add a helper column and specify the number in the denominator (as opposed to manually re-arranging columns to use the Count function). For the sake of example I called the Helper column 'Denominator' in the formula below

Your formula would then become

=COUNTIF([Thumbnail Image Rec'd]@row:[Migration Complete]@row, 1) /Denominator@row

Note I replaced your row numbers with the @row feature. Unless you need to specifically call out a row number it is better to use the @row feature - it is more robust and helps smartsheet speed and performance.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Let me know if I can help further

Kelly

Kelly Moore

Hey @Judy Jefferson

What criteria could be used to exclude the columns? Is there data in other columns that might be leveraged to help with the decision?

Judy Jefferson

Hi @KDM

I don't think there is any other data that can be used. Just have the name of the column and checkboxes. So far the only way this works properly is to place the columns I need next to each other and adjust the formula count that way. Not sure if this is the only this could work.

Kelly Moore

Hey Judy

As you are looking at the data ask yourself how you are deciding how many columns to use. If you can describe that to me, I may be able to help you with the formula.

If there isn't a way for the formula to decide then I would add a helper column and specify the number in the denominator (as opposed to manually re-arranging columns to use the Count function). For the sake of example I called the Helper column 'Denominator' in the formula below

Your formula would then become

=COUNTIF([Thumbnail Image Rec'd]@row:[Migration Complete]@row, 1) /Denominator@row

Note I replaced your row numbers with the @row feature. Unless you need to specifically call out a row number it is better to use the @row feature - it is more robust and helps smartsheet speed and performance.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Let me know if I can help further

Kelly

Judy Jefferson

Hi Kelly,

Apologies if I didn't understand your question earlier.

We have a set of pages are developers are working on that they check off to confirm what work/pages have been completed. This makes up the majority of our spreadsheet. For majority rows on this sheet, these columns would be checked off to show 100% Complete: "Thumbnail Image Rec'd + Updated Download btn with Thumbnail + AEM Page Active-Redirects in place + Migration Complete". So if the formula were correct for the "Articles" section, % Complete should be at 100% for every row:

For a small section of rows on this sheet, only the following would determine 100% Complete: "Thumbnail Image Rec'd + Video File Rec'd + AEM Page Active-Redirects in place + Migration Complete". I didn't check off every row but I'm hoping this makes sense. If the right checkboxes were selected for the "Videos" section, they should be at 100%:

Thanks again for your help looking into this.

Judy

Judy Jefferson

Hi Kim,

I used a helper column as you suggested and used a nesting "IF" statement in the % Complete column which worked for me. Thanks for getting me on the right track and helping me.

Judy