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.
Hi all, I am trying to build out a formula that will return "True" if the CIF #@row can be found above it in the same column (CIF #) AND the Days Since Created is between 30 - 365 days. All of the formulas I have tried have only gotten me so far as the "AND". Adding in the dates screws it up. The goal is to determine if…
I am getting a lot of REF errors for my predecessors and I can't figure out why. At first I thought I might have REF for predecessors to predecessors but that's not the case. When I export the file into Excel it shows the IDs in the place where it shows REF in SS. Each time I refresh the file I feel like different values…
I am trying to create a sheet summary, where the field is a formula that relies on a contact card within a column. However, it keeps returning a result of "#NO MATCH". The columns involved are, 1:) Priority (symbol column) for Up, Unchanged, and Down, and 2:) Contact Card column. I am trying to get a sheet summary formula…
