Closed
jackfood - Nov 23, 2009 at 02:39 AM
jackfood - Nov 23, 2009 at 06:41 AM
Hello,
Using excel 2000.

I am creating a question which needs to creating points for answer.

Cell A187
```Example:
What colour of car is expensive?
blue, yellow, green.```

typing in formula in Cell A188

If a person type blue, Cell A2 will get 1.
If a person type blue and green, Cell A2 will get 2 and so on.

I have 2 codes for this cell but only managed to get 1 point.
`=IF( SUM(--ISNUMBER(SEARCH({"blue","green","yellow"}, A187))),"`
1","0")

another one is
`=IF(ISNUMBER(SEARCH("blue",A187)),1,IF(ISNUMBER(SEARCH("green",A187)),1,0))`

Can anyone improve or give me a new formula. Very much Thanks!!!
Related:

## 2 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Nov 23, 2009 at 05:59 AM
why not this
=IF(A1="blue",1,IF(A1="green",2,IF(A1="yellow",3,"")))
but if i type in "green" for the cell in A1, it will turn 2 instead of 1. Because although "green" is a answer, but only counted as only 1 point.

Condition:
if type in "green and blue", 2 points
if type in "yellow and blue", 2 points
if type in "yellow" 1 point
if type in "green" 1 point
if type in "hello" 0 point
if type in "hello, i am yellow and green people" 2 points