Summing of colored "conditional" cells

jjanusz

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.

jjanusz

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?

J. Craig Williams

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

jjanusz

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.

J. Craig Williams

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.

Craig