Counting text using different criteria & resulting in different va
 

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:D="M Prawn",1)),(IF(Addresses!C:D="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:D=A5,1)),(IF(Addresses!C:D=A6 ,0.5)))

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

Thanks in advance
Rachel

Counting text using different criteria & resulting in different va
 

Try this...

A1 = M Prawn
A2 = 1/2 Prawn

=COUNTIF(Addresses!C:D,A1)+COUNTIF(Addresses!C:D,A 2)/2

--
Biff
Microsoft Excel MVP


"Rachel" wrote in message
...
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:D="M Prawn",1)),(IF(Addresses!C:D="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:D=A5,1)),(IF(Addresses!C:D=A6 ,0.5)))

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

Thanks in advance
Rachel

Counting text using different criteria & resulting in different va
 

Hi,

Try this

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


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rachel" wrote:

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:D="M Prawn",1)),(IF(Addresses!C:D="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:D=A5,1)),(IF(Addresses!C:D=A6 ,0.5)))

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

Thanks in advance
Rachel

Counting text using different criteria & resulting in differen
 

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

"Shane Devenshire" wrote:

Hi,

Try this

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


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rachel" wrote:

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:D="M Prawn",1)),(IF(Addresses!C:D="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:D=A5,1)),(IF(Addresses!C:D=A6 ,0.5)))

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

Thanks in advance
Rachel