Same value when name changes

[lprenrut]

Hi Guys

I need some help with this formula

=IF(A1="chinese",8,0),IF(A1="English",8,0),IF(A1="Spanish",8,0)

How do I continue the formula if the cell A1 name changes to let say English or Spanish etc..

Thanks in advance

Larry

 

[Marcelo Branco]

Maybe...

=IF(ISNUMBER(MATCH(A1,MyList,0)),8,0)
where MyList is a named range that contains Chinese, English, Spanish etc

M.

 

[lprenrut]

Thank you very much M it worked a treat many thanks again

regards

Larry

 

[lprenrut]

Hi Guys again

=IF(ISNUMBER(MATCH(A1,MyList,0)),8,0)

That formula worked well but I want to copy over to cells A2,A3,A4 Etc but add up the total in A6

How can this be achieved

Thanks in advance

Larry

 

[Marcelo Branco]

Could you provide a data sample along with expected results?

M.

 

[lprenrut]

Hi Marcelo

Original formula provided by yourself =IF(ISNUMBER(MATCH(A1,MyList,0)),8,0)

What I want is when cell A1 meets my criteria =mylist this will return a value of 8 in cell A6

When cell A2 meets my criteria =mylist this will add 8 to cell A6 making a total of 16

When cell A3 meets my criteria =mylist this will add 8 to cell A6 making a total of 24 and so on

Hope this makes sense

Larry

 

[Marcelo Branco]

Try

A6
=SUMPRODUCT(8*ISNUMBER(MATCH(A$1:A$5,MyList,0)))

M.

 

[lprenrut]

Marcelo
you are the man!!!

Thank you very much that is exactly what I wanted

Thank you

Larry