Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
Hello All,
I am trying to count or sum how many colored cells there are within a row. The rows consist of test questions and are set to turn red if the condition is not met (answer is incorrect). Is there a way to sum all of the condition not met cells? There are 25 cells that have conditions in them.
From what I have read, I may have to add another column and do an if statement that gives a number if the answer is incorrect and then sum all of those columns together. So for my first formula I would have =IF([PU prevention proper position]1 = "The patient is positioned on his side, with the gluteal fold visible", 0, 1). I would do this for all of the conditions and then sum the ones with numbers together. This seems like a tedious way of doing it. Is there a way to do a sumifs statement and combine them?
How does Smartsheet know that the answer is incorrect?
What you describe DOES sound tedious and I suspect it can be done more cleanly.
Craig
I have to tell it the answer is incorrect. So I can either set a conditional or I found I can do an IF statement and give it a 0 if the answer I tell it to find is the correct one, or a 1 if the answer is anything but. This is the process I ended up using, setting an IF statement for each question and then summing the number of 1's to get how many were incorrect.
So to clarify further:
You have 25 columns with text answers submitted via a Form (users do not have access to the sheet).
You manually select the background or font color if you determine the answer is incorrect.
And you want to count the number of incorrect ones.
Smartsheet does not have a way to count colors, so without knowing anything else, I'd probably put an "answer key" somewhere -- either in a row above the response answers, in a two columns hidden from the normal user, or in a separate sheet.
If you go the row route, you could could them all at once by comparing their answer to the correct answer, but you'd lose your color coding (or it would still be manual)
You could add 25 columns for the formula to determine which answers are correct. You could use that to count and also to set up 25 conditional formatting rules.
There's probably things I haven't thought of, but the end result would be the answers are submitted and the count and coloring take place automatically.
I could build something like that without the colors in less than three hours or less than five with conditional formatting.
If you have budget for paid outside help, contact me (or another consultant). Depending on how often you do this, the return on investment should be pretty quick.
Does anyone know how to revert a sheet to a prior version after a column formula resulted in replacing all of the values with #circular reference? Would all entries have to be manually re-entered?
I know you can use a formula like this: =[Projected Payment Milestones (+)]@row to copy the "value" from one cell to another (because that cell is a formula); however, I need just the "value" formatted as a number ($) to be copied; because in my REPORT I am not able to edit the data as it is recognized as a FORMULA (fx) Is…
I need the Date of Sample Shipment to start within 20 days of the Date of Manufacturing Start Date. However, the predecessor I have there isn't working properly. What should it be? Thank you so much
