IF AND Formulas for multiple answers

Jpurdom

Hi, i'm looking to have a score generated if one or more criteria are met.

Currently, i have

=IF(CONTAINS("Gait Balance", [Why did they not pass Gait?]@row), IF(AND([Why did they not pass Gait?]@row <> "Gait Symmetry", [Why did they not pass Gait?]3 <> "60"), IF(AND([Why did they not pass Gait?]@row <> "Gait Power", [Why did they not pass Gait?]3 <> "60"), "70", "100"), "100"))

I need to add more criteria (Gait Power = 10, Gait Stride - 10, Cognitive =25, and Symptoms = 15

Kelly Moore

Hey@Jpurdom

I'm still trying to understand the process so I can properly help. Have the negative scores already been tallied in your No row such that the Yes row is 100 - No score? In this case the answer is 30? Is the Row#3 fixed, so that the entire sheet refers to only to row 3? I can't tell if this is a rollup metric sheet or if this would be used on an individual so that you would have many Yes rows and many No rows. If you will have many Yes rows and many No rows, you might consider restructuring the sheet so that there were Yes columns and No columns per row.

Here's one approach for your formula. I would think you only have to calculate a Yes sum or a No sum since the score is based on a 100. The formula below looks at the No's, so it is a subtraction from 100. I used the COUNTIFS to easily callout the No row without specifying a row number. The formula looks at the No row and then gathers the count of each respective category. If the COUNTIFS value equals one, which means it found one, it applies the negative score. It sums the cumulative scores and subtracts the Sum from 100. The formula is long because of the number of categories you have.

=100 - (IF(COUNTIFS([Did they pass Sport Gait?]:[Did they pass Sport Gait?], "No", [Why did they not pass Sport Gait?]:[Why did they not pass Sport Gait?], CONTAINS("Cognitive Summary", @cell)) = 1, 25, 0) + IF(COUNTIFS([Did they pass Sport Gait?]:[Did they pass Sport Gait?], "No", [Why did they not pass Sport Gait?]:[Why did they not pass Sport Gait?], CONTAINS("Gait Power", @cell)) = 1, 10, 0) + IF(COUNTIFS([Did they pass Sport Gait?]:[Did they pass Sport Gait?], "No", [Why did they not pass Sport Gait?]:[Why did they not pass Sport Gait?], CONTAINS("Gait Balance", @cell)) = 1, 30, 0) + IF(COUNTIFS([Did they pass Sport Gait?]:[Did they pass Sport Gait?], "No", [Why did they not pass Sport Gait?]:[Why did they not pass Sport Gait?], CONTAINS("Gait Stride", @cell)) = 1, 10, 0)+IF(COUNTIFS([Did they pass Sport Gait?]:[Did they pass Sport Gait?], "No", [Why did they not pass Sport Gait?]:[Why did they not pass Sport Gait?], CONTAINS("Symptom Summary/Self Report", @cell)) = 1, 15, 0)+IF(COUNTIFS([Did they pass Sport Gait?]:[Did they pass Sport Gait?], "No", [Why did they not pass Sport Gait?]:[Why did they not pass Sport Gait?], CONTAINS("Gait Symmetry", @cell)) = 1, 10, 0))

Will this work for you?

Kelly

Kelly Moore

Hey @Jpurdom

I'm happy to help. Is it possible to get a screenshot of your relevant columns, with a couple of rows of data, making sure to include row3 since you called it out specifically? Also, could you describe in words, not your formulas, the criteria for your different scores? Include the new criteria in your description please. I ask for the words as there might be alternative formulas to use once we understand more completely your desired outcome and your sheet structure.

Kelly

Jpurdom

Hi @Kelly Moore. I appreciate your help

The score is out of 100. There could be no issues, one issue, or a multitude of issues identified.

Symptom Summary/Self Report -15 points

Cognitive Summary - 25 Points

Power -10 Points

Balance -30 Points

Symmetry -10 Points

Stride -10 Points

Kelly Moore

Hey@Jpurdom

I'm still trying to understand the process so I can properly help. Have the negative scores already been tallied in your No row such that the Yes row is 100 - No score? In this case the answer is 30? Is the Row#3 fixed, so that the entire sheet refers to only to row 3? I can't tell if this is a rollup metric sheet or if this would be used on an individual so that you would have many Yes rows and many No rows. If you will have many Yes rows and many No rows, you might consider restructuring the sheet so that there were Yes columns and No columns per row.

Here's one approach for your formula. I would think you only have to calculate a Yes sum or a No sum since the score is based on a 100. The formula below looks at the No's, so it is a subtraction from 100. I used the COUNTIFS to easily callout the No row without specifying a row number. The formula looks at the No row and then gathers the count of each respective category. If the COUNTIFS value equals one, which means it found one, it applies the negative score. It sums the cumulative scores and subtracts the Sum from 100. The formula is long because of the number of categories you have.

=100 - (IF(COUNTIFS([Did they pass Sport Gait?]:[Did they pass Sport Gait?], "No", [Why did they not pass Sport Gait?]:[Why did they not pass Sport Gait?], CONTAINS("Cognitive Summary", @cell)) = 1, 25, 0) + IF(COUNTIFS([Did they pass Sport Gait?]:[Did they pass Sport Gait?], "No", [Why did they not pass Sport Gait?]:[Why did they not pass Sport Gait?], CONTAINS("Gait Power", @cell)) = 1, 10, 0) + IF(COUNTIFS([Did they pass Sport Gait?]:[Did they pass Sport Gait?], "No", [Why did they not pass Sport Gait?]:[Why did they not pass Sport Gait?], CONTAINS("Gait Balance", @cell)) = 1, 30, 0) + IF(COUNTIFS([Did they pass Sport Gait?]:[Did they pass Sport Gait?], "No", [Why did they not pass Sport Gait?]:[Why did they not pass Sport Gait?], CONTAINS("Gait Stride", @cell)) = 1, 10, 0)+IF(COUNTIFS([Did they pass Sport Gait?]:[Did they pass Sport Gait?], "No", [Why did they not pass Sport Gait?]:[Why did they not pass Sport Gait?], CONTAINS("Symptom Summary/Self Report", @cell)) = 1, 15, 0)+IF(COUNTIFS([Did they pass Sport Gait?]:[Did they pass Sport Gait?], "No", [Why did they not pass Sport Gait?]:[Why did they not pass Sport Gait?], CONTAINS("Gait Symmetry", @cell)) = 1, 10, 0))

Will this work for you?

Kelly

Jpurdom

@Kelly Moore Has anyone told you that you are amazing today?! Thank you so much. That worked beautifully. Thank you for deciphering my request.

Jpurdom

@Kelly Moore Follow- up question, I would like the formula to reference the cell "Why did they not pass Sport Gait" for the specific row, row 3 is not fixed. I tried to change the @cell to reference @row but that didn't work.

Kelly Moore

@Jpurdom

The @row works for the row you are sitting on. If you have a formula in the 'Yes' row, any field on that row could be called using the @row. To go to a different row, you have to identify the row in some way. A specific row number is one way but will become a potential source of error as time goes forward. There are some options, which is why I used a COUNTIFs formula, to pull additional criteria in that filters down to the correct row. Or, one can restructure the sheet so that you have a grouping of Yes columns and a grouping of No columns on the same row. Then you can easily use the @row across the row to make certain you are collecting the right information. If the number of columns isn't a problem, this is the route I would take. It would also easily allow your assessment to be done using a smartsheet form, if that was desired.

In your current set up is there a person's name or ID number - or something that is unique to that individual- that exists in both the Yes and No rows?

Kelly