How do I exclude a cell with checkbox from formula for a specific row?
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)?
Best Answer

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 rearranging 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.
Let me know if I can help further
Kelly
Answers

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?

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.

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 rearranging 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.
Let me know if I can help further
Kelly

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 ActiveRedirects 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 ActiveRedirects 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

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
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 67 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!