0

I have a single cell that is the output of a survey, that contains items selected from a list of 20 possible items.

ie. Original possible selections:

Ape, Blue, Cat, Red, Dog, Yellow, Pig, Purple, Zebra

User is asked to "select all of the animals," from the list of possible selections. The output places all of the items they've identified into a single cell, separated by commas. A new row is created for each survey entry.

ie.

| User 1 | "Ape, Cat, Pig, Purple" |

| User 2 | "Cat, Red, Dog, Pig, Zebra" |

| User 3 | "Ape, Cat, Dog, Pig, Zebra" | etc...

I have a table with all of the animals and colors, with defined ranges.

ie. animals = A1:A5, and colours = B1:B4

I need to "score" the cell for each user, in a new cell. Where the score value is the count of the number of correctly identified items each counts as 1 point.

ie.

| User 1 | "Ape, Cat, Pig, Purple" | 3 |

| User 2 | "Cat, Red, Dog, Pig, Zebra" | 4 |

| User 3 | "Ape, Cat, Dog, Pig, Zebra" | 5 |

What would the formula need to be for that score cell for each row?

I found a previous thread, that seems to point in the right direction, Excel: Searching for multiple terms in a cell But this only checks for the existence of any of the items in a cell from a list and returns a true or false

Thanks for anyone's help!

Mustard
  • 33
  • 3
  • Seems like you should also be subtracting 1 for each *wrong* item, or you could get perfect scores by always selecting all items? – Tim Williams Jul 09 '18 at 23:19
  • Indeed! I figured once I had the first part, I could figure out the rest... which I did, btw, using the answer I marked! – Mustard Jul 11 '18 at 02:51

3 Answers3

2

COUNTIF with SUMPRODUCT:

=SUMPRODUCT(COUNTIF(D2,"*" & $A$1:$A$5 & "*"))

Which also has the limitation of the amimals not being a sub-string, like Ant and Ant-Eater

enter image description here

If sub-strings are a problem then use this:

=SUMPRODUCT(--(ISNUMBER(SEARCH(", " & $A$1:$A$5 & ", ",", " & D2 & ", "))))

This will make a complete match between the commas.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Thank you, this worked as desired. I further extended it, btw, to include subtracting the incorrectly selected colours, using the following formula: `(SUMPRODUCT(COUNTIF(D2,"*" & $A$1:$A$5 & "*"))) - (SUMPRODUCT(COUNTIF(D2,"*" & $B$1:$B$4 & "*"))` – Mustard Jul 10 '18 at 20:21
0

The formula shown is entered in D3 (an array formula, so use Ctrl+Shift+Enter) and filled down to D5

A3:A6 is a named range "animals"

Note this is only reliable if none of your terms are sub-strings of another term.

enter image description here

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
0

If you do not like to use the formulas above, which are very efficient and most ideal, a simpler but longer way would be as follows:

select the animals--> Data--> Text to Columns, and split them into columns with the separator being a comma

Once this is done, do a countif on each column, and it will total it up for you. You will need to do 20 countifs though, so it is far from ideal IE 

=countifs(column which it could be in],[no.1 animal])+
countifs(column which it could be in],[no.2 animal])+...
countifs(column which it could be in],[no.20 animal])

This is easy to see how it works and if you receive more answers you will have to split them out again

Tejkaran Samra
  • 96
  • 1
  • 14