Here is an "IF" statement for cell E10:

=IF(OR(E10={"la","pd","ea","pdx","mc","v","pr"}),7 .75,IF(OR(F10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,IF(OR(G 10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,IF(OR(H 10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,IF(OR(I 10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,0))) ))

Is there a function to allow me to count how many times a certain text
is repeated and have that multiplied by 7.75? For example, in the
above "IF" statement, suppose E10 has "la", H10 has "v", and I10 has
"pr". I would like to have the function recognize 3 instances of text
and be multiplied by 7.75 to get 23.25 (or just have 23.25 in the cell
whenever 3 instances of text are in cells E10:I10). Any suggestions
are welcomed. Thanks.

One way:

=7.75*SUM(COUNTIF(E10:I10,
{"la","pd","ea","pdx","mc","v","pr"}))

HTH
Jason
Atlanta, GA

-----Original Message-----
Here is an "IF" statement for cell E10:

=IF(OR(E10={"la","pd","ea","pdx","mc","v","pr"}), 7.75,IF
(OR(F10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,IF(OR( G10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,IF(OR( H10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,IF(OR( I10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,0))) ))

Is there a function to allow me to count how many times
a certain text
is repeated and have that multiplied by 7.75? For
example, in the
above "IF" statement, suppose E10 has "la", H10 has "v",
and I10 has
"pr". I would like to have the function recognize 3
instances of text
and be multiplied by 7.75 to get 23.25 (or just have
23.25 in the cell
whenever 3 instances of text are in cells E10:I10). Any
suggestions
are welcomed. Thanks.

.

Try in say, J10:

=7.75*SUMPRODUCT(--NOT(ISNA(MATCH(E10:I10,{"la";"pd";"ea";"pdx";"mc"; "v";"pr
"},0))))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Tourcat" wrote in message
oups.com...
Here is an "IF" statement for cell E10:

=IF(OR(E10={"la","pd","ea","pdx","mc","v","pr"}),7 .75,IF(OR(F10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,IF(OR(G 10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,IF(OR(H 10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,IF(OR(I 10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,0))) ))

Is there a function to allow me to count how many times a certain text
is repeated and have that multiplied by 7.75? For example, in the
above "IF" statement, suppose E10 has "la", H10 has "v", and I10 has
"pr". I would like to have the function recognize 3 instances of text
and be multiplied by 7.75 to get 23.25 (or just have 23.25 in the cell
whenever 3 instances of text are in cells E10:I10). Any suggestions
are welcomed. Thanks.

Thanks that worked great, but what about the following situation. What
if, for example, cells E8:H8 were multiplied by 7.75 and cell I8 needed
to be multiplied by 4.75, can both be combined into one function for
one cell. Here is what I have for cell J8 so far, for example:

=7.75*SUMPRODUCT(--NOT(ISNA(MATCH(E8:H8,{"LA";"PD";"EA";"PDX";"MC";"V ";"PR

"},0))))

I need cell I8 to be included above, but the SUMPRODUCT would need to
be multiplied by 4.75 just for that one cell. Any suggestions? Thanks.

You can shorten the formula a bit by using ISNUMBER instead of NOT(ISNA(...))
and by moving the multiplication inside the sumproduct formula (which
eliminates the need for the --), i.e.

=SUMPRODUCT(7.75*ISNUMBER(MATCH(E8:H8,{"LA";"PD";" EA";"PDX";"MC";"V";"PR"},0)))+
4.75*(ISNUMBER(MATCH(I8,{"LA";"PD";"EA";"PDX";"MC" ;"V";"PR"},0)))

Or, IF none of the cells E8:H8 will ever be blank,

=SUMPRODUCT(7.75*ISNUMBER(FIND(E8:H8,"LA/PD/EA/PDX/MC/V/PR")))+4.75*ISNUMBER(FIND(I8,"LA/PD/EA/PDX/MC/V/PR"))

On 9 Feb 2005 08:23:35 -0800, "Tourcat" wrote:

Thanks that worked great, but what about the following situation. What
if, for example, cells E8:H8 were multiplied by 7.75 and cell I8 needed
to be multiplied by 4.75, can both be combined into one function for
one cell. Here is what I have for cell J8 so far, for example:

=7.75*SUMPRODUCT(--NOT(ISNA(MATCH(E8:H8,{"LA";"PD";"EA";"PDX";"MC";"V ";"PR

"},0))))

I need cell I8 to be included above, but the SUMPRODUCT would need to
be multiplied by 4.75 just for that one cell. Any suggestions? Thanks.

I'm puzzled by your comment, "whenever 3 instances of text are in cells
E10:I10". Do you mean that the cells will either be blank OR contain one of
these 7 text strings, never other text like "abc" or numbers, etc? If that is
correct, you can certainly simplify things.

=COUNTA(E10:H10)*7.75+COUNTA(I10)*4.75


On 9 Feb 2005 06:06:20 -0800, "Tourcat" wrote:

Here is an "IF" statement for cell E10:

=IF(OR(E10={"la","pd","ea","pdx","mc","v","pr"}), 7.75,IF(OR(F10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,IF(OR( G10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,IF(OR( H10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,IF(OR( I10=
{"la","pd","ea","pdx","mc","v","pr"}),7.75,0))) ))

Is there a function to allow me to count how many times a certain text
is repeated and have that multiplied by 7.75? For example, in the
above "IF" statement, suppose E10 has "la", H10 has "v", and I10 has
"pr". I would like to have the function recognize 3 instances of text
and be multiplied by 7.75 to get 23.25 (or just have 23.25 in the cell
whenever 3 instances of text are in cells E10:I10). Any suggestions
are welcomed. Thanks.

Thanks for the follow-through and the refinements, Myrna !
Priceless touches of experience ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Myrna Larson" wrote in message
...
You can shorten the formula a bit by using ISNUMBER instead of
NOT(ISNA(...))
and by moving the multiplication inside the sumproduct formula (which
eliminates the need for the --), i.e.


=SUMPRODUCT(7.75*ISNUMBER(MATCH(E8:H8,{"LA";"PD";" EA";"PDX";"MC";"V";"PR"},0
)))+
4.75*(ISNUMBER(MATCH(I8,{"LA";"PD";"EA";"PDX";"MC" ;"V";"PR"},0)))

Or, IF none of the cells E8:H8 will ever be blank,


=SUMPRODUCT(7.75*ISNUMBER(FIND(E8:H8,"LA/PD/EA/PDX/MC/V/PR")))+4.75*ISNUMBER
(FIND(I8,"LA/PD/EA/PDX/MC/V/PR"))

On 9 Feb 2005 08:23:35 -0800, "Tourcat"
wrote:

Thanks that worked great, but what about the following situation. What
if, for example, cells E8:H8 were multiplied by 7.75 and cell I8 needed
to be multiplied by 4.75, can both be combined into one function for
one cell. Here is what I have for cell J8 so far, for example:


=7.75*SUMPRODUCT(--NOT(ISNA(MATCH(E8:H8,{"LA";"PD";"EA";"PDX";"MC";"V ";"PR

"},0))))

I need cell I8 to be included above, but the SUMPRODUCT would need to
be multiplied by 4.75 just for that one cell. Any suggestions? Thanks.