How to combine 2 different SUMPRODUCT criteria into one cell?????

[Tourcat]

Here is what I have as a function for cell J8, for example:

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

But, I need to include cell I8 into the calculation, but it would be
multiplied by 4.75, instead of 7.75. What I'm doing, basically, is to
determine the occurences of 7 different text strings in cells E8:I8,
but I8 needs to be multiplied by 4.75, rather than 7.75. There may be
a simple answer, but I don't know how. Thanks.

[Don Guillett]

could it be as simple as =yourformula+(4.75*i8)

--
Don Guillett
SalesAid Software
[email protected]
"Tourcat" <[email protected]> wrote in message
news:[email protected]...
> Here is what I have as a function for cell J8, for example:
>
> =7.75*SUMPRODUCT(--NOT(ISNA(MATCH(E8:H8,
> {"LA";"PD";"EA";"PDX";"MC";"V";"PR"},0))))
>
> But, I need to include cell I8 into the calculation, but it would be
> multiplied by 4.75, instead of 7.75. What I'm doing, basically, is to
> determine the occurences of 7 different text strings in cells E8:I8,
> but I8 needs to be multiplied by 4.75, rather than 7.75. There may be
> a simple answer, but I don't know how. Thanks.
>

[Myrna Larson]

I don't think so, Don. I8 also has to contain one of those strings.

On Wed, 9 Feb 2005 14:24:53 -0600, "Don Guillett" <[email protected]> wrote:

>could it be as simple as =yourformula+(4.75*i8)

[Aladin Akyurek]

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

Note that NOT(ISNA(MATCH(...))) == ISNUMBER(MATCH(...))

Tourcat wrote:
> Here is what I have as a function for cell J8, for example:
>
> =7.75*SUMPRODUCT(--NOT(ISNA(MATCH(E8:H8,
> {"LA";"PD";"EA";"PDX";"MC";"V";"PR"},0))))
>
> But, I need to include cell I8 into the calculation, but it would be
> multiplied by 4.75, instead of 7.75. What I'm doing, basically, is to
> determine the occurences of 7 different text strings in cells E8:I8,
> but I8 needs to be multiplied by 4.75, rather than 7.75. There may be
> a simple answer, but I don't know how. Thanks.
>

[Max]

In case you missed it,
here's Myrna's suggestions posted in your original thread:

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

... "
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Tourcat" <[email protected]> wrote in message
news:[email protected]...
> Here is what I have as a function for cell J8, for example:
>
> =7.75*SUMPRODUCT(--NOT(ISNA(MATCH(E8:H8,
> {"LA";"PD";"EA";"PDX";"MC";"V";"PR"},0))))
>
> But, I need to include cell I8 into the calculation, but it would be
> multiplied by 4.75, instead of 7.75. What I'm doing, basically, is to
> determine the occurences of 7 different text strings in cells E8:I8,
> but I8 needs to be multiplied by 4.75, rather than 7.75. There may be
> a simple answer, but I don't know how. Thanks.
>