How to add "IF" statements?

[Tourcat]

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.

 

[Jason Morin]

One way:

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

HTH
Jason
Atlanta, GA

 

[Max]

Try in say, J10:

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

 

[Tourcat]

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.

 

[Myrna Larson]

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"))

 

[Myrna Larson]

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

 

[Max]

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 <at>yahoo<dot>com
----

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"))