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

Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!
Unparsable formula; Sheet summary SUMIF formula
Can anyone help why this formula is coming up as unparseable? I'm creating a sheet summary, where it totals the Estimated GP (Est. GP) column if the Status column has the "Quoted" drop-down option is selected. Formula i've used: =SUMIF(Status:Status,"Quoted",Est. GP:Est. GP) Thanks a lot!
INDEX/MATCH Most Recent Entry
I am trying to pull from a sheet that is essentially a list of new records that are entered weekly. To keep things clean I have a helper sheet that just lists the project name once and the rest of the columns pull in through an INDEX/MATCH. I am trying to have that helper sheet pull in only the most recent entry. There is…