Summing of colored "conditional" cells

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.

Find more posts tagged with

Healthcare

Formulas

Conditional Formatting

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…