Counting text using different criteria & resulting in different va

[Rachel]

Hi,
I need to find and count certain words in a column and for each word have a
different value and then add those values?
EG
=SUM((IF(Addresses!C="M Prawn",1)),(IF(Addresses!C="1/2 Prawn",0.5)))

Is this possible? I need any cell in the range containing "M Prawn" to be
worth 1 and any cell containing "1/2 Prawn" to be worth 0.5. Therefore if
there is 1 cell of each the result would be 1.5.

Also I don't want to actually have the "M Prawn" or "1/2 Prawn" in the
formula. I want to have a cell reference to a separate cell that contains the
words "M Prawn" etc.
EG
=SUM((IF(Addresses!C=A5,1)),(IF(Addresses!C=A6,0.5)))

What am I doing wrong or is there a better way?

Thanks in advance
Rachel

 

[T. Valko]

Try this...

A1 = M Prawn
A2 = 1/2 Prawn

=COUNTIF(Addresses!C,A1)+COUNTIF(Addresses!C,A2)/2

 

[Shane Devenshire]

Hi,

Try this

=SUMPRODUCT((Addresses!C=A5)+0.5*(Addresses!C=A6))

 

[Rachel]

Hi there, thanks for your responses. Both suggestions work.
Much appreciated!
Rachel