SUMPRODUCT using and INDEX function doesn't total

I could use some help determining my problem with a Sumproduct function. I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where I'm getting in trouble.
I have a separate database or multi-row/multi-column array (pl_Provider) with the first column a listing of items and in the third column of the array I have assigned a value of "Yes" or "No" in the same row for each item.

I'm having the formula look at a local range "G8:G14" and then do a lookup in the array "pl_Provider" to determine if a Matching record from column G exists and if it does to return the value from column 3, "Yes" or "No". If it is Yes, I want to include the value in column M in the sum.

=SUMPRODUCT(--(INDEX(pl_Provider, MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))

The first array in Sumproduct doesn't seem to be giving me an array value, but seems to only evaluate it for only the first value. What don't I understand???

I assume that:

(1) pl_Resources refers to the first column of pl_Provider,
(2) pl_Provider is set (sorted) in ascending order on pl_provider.

[A] If G8:G14 is guaranteed not to contain any item that does not also
exist pl_Resources, then:

=SUMPRODUCT(--(LOOKUP(G8:G14,pl_Provider)="Yes"),M8:M14)

If Yes/No values are not in the last column of pl_Provider...

=SUMPRODUCT(--(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3))="Yes"),M8:M14)

[B] If G8:G14 might contain items that do not exist in pl_Resources, then:

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G14,pl_Provider)="Yes")),M8:M14)

If Yes/No values are not in the last column of pl_Provider...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3)),M8:M14)


rlutes wrote:
> I could use some help determining my problem with a Sumproduct function.
> I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where
> I'm getting in trouble.
> I have a separate database or multi-row/multi-column array
> (pl_Provider) with the first column a listing of items and in the third
> column of the array I have assigned a value of "Yes" or "No" in the same
> row for each item.
>
> I'm having the formula look at a local range "G8:G14" and then do a
> lookup in the array "pl_Provider" to determine if a Matching record
> from column G exists and if it does to return the value from column 3,
> "Yes" or "No". If it is Yes, I want to include the value in column M
> in the sum.
>
> =SUMPRODUCT(--(INDEX(pl_Provider,
> MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))
>
> The first array in Sumproduct doesn't seem to be giving me an array
> value, but seems to only evaluate it for only the first value. What
> don't I understand???
>
>

Great, it works! I had spent a couple of hours working on this.

One change I had to make, was to change my "Yes/No" in the table to "1/0" in order to make it work, but that wasn't a problem to do and actually is a cleaner way. This solution lets me have a flexible table which can be added to without having to reprogram.
Thanks for the help!

The following formula...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
(G8:G14,pl_Provider)="Yes")),M8:M14)

....seems to fail when any one of the array of lookup values returns a
#N/A value. Therefore, wouldn't the following formula be more
appropriate?

=SUM(IF(ISNUMBER(MATCH(G8:G14,pl_Resources,0)),(LOOKUP(G8:G14,pl_Provider
)="Yes")* M8:M14))

....confirmed with CONTROL+SHIFT+ENTER.

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> I assume that:
>
> (1) pl_Resources refers to the first column of pl_Provider,
> (2) pl_Provider is set (sorted) in ascending order on pl_provider.
>
> [A] If G8:G14 is guaranteed not to contain any item that does not also
> exist pl_Resources, then:
>
> =SUMPRODUCT(--(LOOKUP(G8:G14,pl_Provider)="Yes"),M8:M14)
>
> If Yes/No values are not in the last column of pl_Provider...
>
> =SUMPRODUCT(--(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3))="Yes"),M8:M1
> 4)
>
> [B] If G8:G14 might contain items that do not exist in pl_Resources, then:
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G
> 14,pl_Provider)="Yes")),M8:M14)
>
> If Yes/No values are not in the last column of pl_Provider...
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G
> 14,pl_Resources,INDEX(pl_Provider,0,3)),M8:M14)
>
>
> rlutes wrote:
> > I could use some help determining my problem with a Sumproduct function.
> > I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where
> > I'm getting in trouble.
> > I have a separate database or multi-row/multi-column array
> > (pl_Provider) with the first column a listing of items and in the third
> > column of the array I have assigned a value of "Yes" or "No" in the same
> > row for each item.
> >
> > I'm having the formula look at a local range "G8:G14" and then do a
> > lookup in the array "pl_Provider" to determine if a Matching record
> > from column G exists and if it does to return the value from column 3,
> > "Yes" or "No". If it is Yes, I want to include the value in column M
> > in the sum.
> >
> > =SUMPRODUCT(--(INDEX(pl_Provider,
> > MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))
> >
> > The first array in Sumproduct doesn't seem to be giving me an array
> > value, but seems to only evaluate it for only the first value. What
> > don't I understand???
> >
> >

Domenic wrote:
> The following formula...
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
> (G8:G14,pl_Provider)="Yes")),M8:M14)
>
> ...seems to fail when any one of the array of lookup values returns a
> #N/A value.

[...]

An #N/A cannot go thru the ISNUMBER tests that figure in the formula.

Here is an example intermediate state of evaluation:

=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> An #N/A cannot go thru the ISNUMBER tests that figure in the formula.
>
> Here is an example intermediate state of evaluation:
>
> =SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"
> Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

In theory, the above formula should return 4. The problem is that it
returns 0 and I get the following error message...

"Microsoft Excel cannot calculate the formula..."

I understand that the next stages of the evaluation would be as
follows...

=SUMPRODUCT({0;1;1;0;1;0},{0;0;1;0;0;0},{5;6;4;7;8;9})

=SUMPRODUCT({0;0;4;0;0;0})

So why am I getting an error?

Domenic wrote:
> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
>
>>An #N/A cannot go thru the ISNUMBER tests that figure in the formula.
>>
>>Here is an example intermediate state of evaluation:
>>
>>=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"
>>Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})
>
>
> In theory, the above formula should return 4. The problem is that it
> returns 0 and I get the following error message...
>
> "Microsoft Excel cannot calculate the formula..."
>
> I understand that the next stages of the evaluation would be as
> follows...
>
> =SUMPRODUCT({0;1;1;0;1;0},{0;0;1;0;0;0},{5;6;4;7;8;9})
>
> =SUMPRODUCT({0;0;4;0;0;0})
>
> So why am I getting an error?

No idea. I myself get 4. Want me to send you the workbook?

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> No idea. I myself get 4. Want me to send you the workbook?

Yes please. I'd appreciate it.

Thanks Aladin!

When I opened the workbook, the correct result was shown. But when I
re-entered the formula or clicked in the formula bar and then pressed
ENTER, I got a 0 along with the same error message.

So something must be wrong with my version of Excel. Maybe a bug of
some sort, who knows. I'll have to bring this to Microsoft's attention.

Aladin, thank you very much for your help! Much appreciated!

In article <[email protected]>,
Domenic <[email protected]> wrote:

> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
> > No idea. I myself get 4. Want me to send you the workbook?
>
> Yes please. I'd appreciate it.
>
> Thanks Aladin!

Domenic wrote:
> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
>
>>An #N/A cannot go thru the ISNUMBER tests that figure in the formula.
>>
>>Here is an example intermediate state of evaluation:
>>
>>=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"
>>Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})
>
>
> In theory, the above formula should return 4. The problem is that it
> returns 0 and I get the following error message...
>
> "Microsoft Excel cannot calculate the formula..."
>
> I understand that the next stages of the evaluation would be as
> follows...
>
> =SUMPRODUCT({0;1;1;0;1;0},{0;0;1;0;0;0},{5;6;4;7;8;9})
>
> =SUMPRODUCT({0;0;4;0;0;0})
>
> So why am I getting an error?

No idea. I myself get 4. Want me to send you the workbook?

When I opened the workbook, the correct result was shown. But when I
re-entered the formula or clicked in the formula bar and then pressed
ENTER, I got a 0 along with the same error message.

So something must be wrong with my version of Excel. Maybe a bug of
some sort, who knows. I'll have to bring this to Microsoft's attention.

Aladin, thank you very much for your help! Much appreciated!

In article <[email protected]>,
Domenic <[email protected]> wrote:

> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
> > No idea. I myself get 4. Want me to send you the workbook?
>
> Yes please. I'd appreciate it.
>
> Thanks Aladin!

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> No idea. I myself get 4. Want me to send you the workbook?

Yes please. I'd appreciate it.

Thanks Aladin!

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> An #N/A cannot go thru the ISNUMBER tests that figure in the formula.
>
> Here is an example intermediate state of evaluation:
>
> =SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"
> Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

In theory, the above formula should return 4. The problem is that it
returns 0 and I get the following error message...

"Microsoft Excel cannot calculate the formula..."

I understand that the next stages of the evaluation would be as
follows...

=SUMPRODUCT({0;1;1;0;1;0},{0;0;1;0;0;0},{5;6;4;7;8;9})

=SUMPRODUCT({0;0;4;0;0;0})

So why am I getting an error?

Domenic wrote:
> The following formula...
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
> (G8:G14,pl_Provider)="Yes")),M8:M14)
>
> ...seems to fail when any one of the array of lookup values returns a
> #N/A value.

[...]

An #N/A cannot go thru the ISNUMBER tests that figure in the formula.

Here is an example intermediate state of evaluation:

=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

The following formula...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
(G8:G14,pl_Provider)="Yes")),M8:M14)

....seems to fail when any one of the array of lookup values returns a
#N/A value. Therefore, wouldn't the following formula be more
appropriate?

=SUM(IF(ISNUMBER(MATCH(G8:G14,pl_Resources,0)),(LOOKUP(G8:G14,pl_Provider
)="Yes")* M8:M14))

....confirmed with CONTROL+SHIFT+ENTER.

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> I assume that:
>
> (1) pl_Resources refers to the first column of pl_Provider,
> (2) pl_Provider is set (sorted) in ascending order on pl_provider.
>
> [A] If G8:G14 is guaranteed not to contain any item that does not also
> exist pl_Resources, then:
>
> =SUMPRODUCT(--(LOOKUP(G8:G14,pl_Provider)="Yes"),M8:M14)
>
> If Yes/No values are not in the last column of pl_Provider...
>
> =SUMPRODUCT(--(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3))="Yes"),M8:M1
> 4)
>
> [B] If G8:G14 might contain items that do not exist in pl_Resources, then:
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G
> 14,pl_Provider)="Yes")),M8:M14)
>
> If Yes/No values are not in the last column of pl_Provider...
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G
> 14,pl_Resources,INDEX(pl_Provider,0,3)),M8:M14)
>
>
> rlutes wrote:
> > I could use some help determining my problem with a Sumproduct function.
> > I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where
> > I'm getting in trouble.
> > I have a separate database or multi-row/multi-column array
> > (pl_Provider) with the first column a listing of items and in the third
> > column of the array I have assigned a value of "Yes" or "No" in the same
> > row for each item.
> >
> > I'm having the formula look at a local range "G8:G14" and then do a
> > lookup in the array "pl_Provider" to determine if a Matching record
> > from column G exists and if it does to return the value from column 3,
> > "Yes" or "No". If it is Yes, I want to include the value in column M
> > in the sum.
> >
> > =SUMPRODUCT(--(INDEX(pl_Provider,
> > MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))
> >
> > The first array in Sumproduct doesn't seem to be giving me an array
> > value, but seems to only evaluate it for only the first value. What
> > don't I understand???
> >
> >

I assume that:

(1) pl_Resources refers to the first column of pl_Provider,
(2) pl_Provider is set (sorted) in ascending order on pl_provider.

[A] If G8:G14 is guaranteed not to contain any item that does not also
exist pl_Resources, then:

=SUMPRODUCT(--(LOOKUP(G8:G14,pl_Provider)="Yes"),M8:M14)

If Yes/No values are not in the last column of pl_Provider...

=SUMPRODUCT(--(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3))="Yes"),M8:M14)

[B] If G8:G14 might contain items that do not exist in pl_Resources, then:

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G14,pl_Provider)="Yes")),M8:M14)

If Yes/No values are not in the last column of pl_Provider...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3)),M8:M14)


rlutes wrote:
> I could use some help determining my problem with a Sumproduct function.
> I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where
> I'm getting in trouble.
> I have a separate database or multi-row/multi-column array
> (pl_Provider) with the first column a listing of items and in the third
> column of the array I have assigned a value of "Yes" or "No" in the same
> row for each item.
>
> I'm having the formula look at a local range "G8:G14" and then do a
> lookup in the array "pl_Provider" to determine if a Matching record
> from column G exists and if it does to return the value from column 3,
> "Yes" or "No". If it is Yes, I want to include the value in column M
> in the sum.
>
> =SUMPRODUCT(--(INDEX(pl_Provider,
> MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))
>
> The first array in Sumproduct doesn't seem to be giving me an array
> value, but seems to only evaluate it for only the first value. What
> don't I understand???
>
>

I assume that:

(1) pl_Resources refers to the first column of pl_Provider,
(2) pl_Provider is set (sorted) in ascending order on pl_provider.

[A] If G8:G14 is guaranteed not to contain any item that does not also
exist pl_Resources, then:

=SUMPRODUCT(--(LOOKUP(G8:G14,pl_Provider)="Yes"),M8:M14)

If Yes/No values are not in the last column of pl_Provider...

=SUMPRODUCT(--(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3))="Yes"),M8:M14)

[B] If G8:G14 might contain items that do not exist in pl_Resources, then:

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G14,pl_Provider)="Yes")),M8:M14)

If Yes/No values are not in the last column of pl_Provider...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3)),M8:M14)


rlutes wrote:
> I could use some help determining my problem with a Sumproduct function.
> I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where
> I'm getting in trouble.
> I have a separate database or multi-row/multi-column array
> (pl_Provider) with the first column a listing of items and in the third
> column of the array I have assigned a value of "Yes" or "No" in the same
> row for each item.
>
> I'm having the formula look at a local range "G8:G14" and then do a
> lookup in the array "pl_Provider" to determine if a Matching record
> from column G exists and if it does to return the value from column 3,
> "Yes" or "No". If it is Yes, I want to include the value in column M
> in the sum.
>
> =SUMPRODUCT(--(INDEX(pl_Provider,
> MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))
>
> The first array in Sumproduct doesn't seem to be giving me an array
> value, but seems to only evaluate it for only the first value. What
> don't I understand???
>
>

The following formula...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
(G8:G14,pl_Provider)="Yes")),M8:M14)

....seems to fail when any one of the array of lookup values returns a
#N/A value. Therefore, wouldn't the following formula be more
appropriate?

=SUM(IF(ISNUMBER(MATCH(G8:G14,pl_Resources,0)),(LOOKUP(G8:G14,pl_Provider
)="Yes")* M8:M14))

....confirmed with CONTROL+SHIFT+ENTER.

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> I assume that:
>
> (1) pl_Resources refers to the first column of pl_Provider,
> (2) pl_Provider is set (sorted) in ascending order on pl_provider.
>
> [A] If G8:G14 is guaranteed not to contain any item that does not also
> exist pl_Resources, then:
>
> =SUMPRODUCT(--(LOOKUP(G8:G14,pl_Provider)="Yes"),M8:M14)
>
> If Yes/No values are not in the last column of pl_Provider...
>
> =SUMPRODUCT(--(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3))="Yes"),M8:M1
> 4)
>
> [B] If G8:G14 might contain items that do not exist in pl_Resources, then:
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G
> 14,pl_Provider)="Yes")),M8:M14)
>
> If Yes/No values are not in the last column of pl_Provider...
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G
> 14,pl_Resources,INDEX(pl_Provider,0,3)),M8:M14)
>
>
> rlutes wrote:
> > I could use some help determining my problem with a Sumproduct function.
> > I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where
> > I'm getting in trouble.
> > I have a separate database or multi-row/multi-column array
> > (pl_Provider) with the first column a listing of items and in the third
> > column of the array I have assigned a value of "Yes" or "No" in the same
> > row for each item.
> >
> > I'm having the formula look at a local range "G8:G14" and then do a
> > lookup in the array "pl_Provider" to determine if a Matching record
> > from column G exists and if it does to return the value from column 3,
> > "Yes" or "No". If it is Yes, I want to include the value in column M
> > in the sum.
> >
> > =SUMPRODUCT(--(INDEX(pl_Provider,
> > MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))
> >
> > The first array in Sumproduct doesn't seem to be giving me an array
> > value, but seems to only evaluate it for only the first value. What
> > don't I understand???
> >
> >

Domenic wrote:
> The following formula...
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
> (G8:G14,pl_Provider)="Yes")),M8:M14)
>
> ...seems to fail when any one of the array of lookup values returns a
> #N/A value.

[...]

An #N/A cannot go thru the ISNUMBER tests that figure in the formula.

Here is an example intermediate state of evaluation:

=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> An #N/A cannot go thru the ISNUMBER tests that figure in the formula.
>
> Here is an example intermediate state of evaluation:
>
> =SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"
> Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

In theory, the above formula should return 4. The problem is that it
returns 0 and I get the following error message...

"Microsoft Excel cannot calculate the formula..."

I understand that the next stages of the evaluation would be as
follows...

=SUMPRODUCT({0;1;1;0;1;0},{0;0;1;0;0;0},{5;6;4;7;8;9})

=SUMPRODUCT({0;0;4;0;0;0})

So why am I getting an error?

Domenic wrote:
> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
>
>>An #N/A cannot go thru the ISNUMBER tests that figure in the formula.
>>
>>Here is an example intermediate state of evaluation:
>>
>>=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"
>>Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})
>
>
> In theory, the above formula should return 4. The problem is that it
> returns 0 and I get the following error message...
>
> "Microsoft Excel cannot calculate the formula..."
>
> I understand that the next stages of the evaluation would be as
> follows...
>
> =SUMPRODUCT({0;1;1;0;1;0},{0;0;1;0;0;0},{5;6;4;7;8;9})
>
> =SUMPRODUCT({0;0;4;0;0;0})
>
> So why am I getting an error?

No idea. I myself get 4. Want me to send you the workbook?

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> No idea. I myself get 4. Want me to send you the workbook?

Yes please. I'd appreciate it.

Thanks Aladin!

When I opened the workbook, the correct result was shown. But when I
re-entered the formula or clicked in the formula bar and then pressed
ENTER, I got a 0 along with the same error message.

So something must be wrong with my version of Excel. Maybe a bug of
some sort, who knows. I'll have to bring this to Microsoft's attention.

Aladin, thank you very much for your help! Much appreciated!

In article <[email protected]>,
Domenic <[email protected]> wrote:

> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
> > No idea. I myself get 4. Want me to send you the workbook?
>
> Yes please. I'd appreciate it.
>
> Thanks Aladin!

Domenic wrote:
> The following formula...
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
> (G8:G14,pl_Provider)="Yes")),M8:M14)
>
> ...seems to fail when any one of the array of lookup values returns a
> #N/A value.

[...]

An #N/A cannot go thru the ISNUMBER tests that figure in the formula.

Here is an example intermediate state of evaluation:

=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

When I opened the workbook, the correct result was shown. But when I
re-entered the formula or clicked in the formula bar and then pressed
ENTER, I got a 0 along with the same error message.

So something must be wrong with my version of Excel. Maybe a bug of
some sort, who knows. I'll have to bring this to Microsoft's attention.

Aladin, thank you very much for your help! Much appreciated!

In article <[email protected]>,
Domenic <[email protected]> wrote:

> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
> > No idea. I myself get 4. Want me to send you the workbook?
>
> Yes please. I'd appreciate it.
>
> Thanks Aladin!

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> No idea. I myself get 4. Want me to send you the workbook?

Yes please. I'd appreciate it.

Thanks Aladin!

Domenic wrote:
> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
>
>>An #N/A cannot go thru the ISNUMBER tests that figure in the formula.
>>
>>Here is an example intermediate state of evaluation:
>>
>>=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"
>>Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})
>
>
> In theory, the above formula should return 4. The problem is that it
> returns 0 and I get the following error message...
>
> "Microsoft Excel cannot calculate the formula..."
>
> I understand that the next stages of the evaluation would be as
> follows...
>
> =SUMPRODUCT({0;1;1;0;1;0},{0;0;1;0;0;0},{5;6;4;7;8;9})
>
> =SUMPRODUCT({0;0;4;0;0;0})
>
> So why am I getting an error?

No idea. I myself get 4. Want me to send you the workbook?

The following formula...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
(G8:G14,pl_Provider)="Yes")),M8:M14)

....seems to fail when any one of the array of lookup values returns a
#N/A value. Therefore, wouldn't the following formula be more
appropriate?

=SUM(IF(ISNUMBER(MATCH(G8:G14,pl_Resources,0)),(LOOKUP(G8:G14,pl_Provider
)="Yes")* M8:M14))

....confirmed with CONTROL+SHIFT+ENTER.

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> I assume that:
>
> (1) pl_Resources refers to the first column of pl_Provider,
> (2) pl_Provider is set (sorted) in ascending order on pl_provider.
>
> [A] If G8:G14 is guaranteed not to contain any item that does not also
> exist pl_Resources, then:
>
> =SUMPRODUCT(--(LOOKUP(G8:G14,pl_Provider)="Yes"),M8:M14)
>
> If Yes/No values are not in the last column of pl_Provider...
>
> =SUMPRODUCT(--(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3))="Yes"),M8:M1
> 4)
>
> [B] If G8:G14 might contain items that do not exist in pl_Resources, then:
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G
> 14,pl_Provider)="Yes")),M8:M14)
>
> If Yes/No values are not in the last column of pl_Provider...
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G
> 14,pl_Resources,INDEX(pl_Provider,0,3)),M8:M14)
>
>
> rlutes wrote:
> > I could use some help determining my problem with a Sumproduct function.
> > I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where
> > I'm getting in trouble.
> > I have a separate database or multi-row/multi-column array
> > (pl_Provider) with the first column a listing of items and in the third
> > column of the array I have assigned a value of "Yes" or "No" in the same
> > row for each item.
> >
> > I'm having the formula look at a local range "G8:G14" and then do a
> > lookup in the array "pl_Provider" to determine if a Matching record
> > from column G exists and if it does to return the value from column 3,
> > "Yes" or "No". If it is Yes, I want to include the value in column M
> > in the sum.
> >
> > =SUMPRODUCT(--(INDEX(pl_Provider,
> > MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))
> >
> > The first array in Sumproduct doesn't seem to be giving me an array
> > value, but seems to only evaluate it for only the first value. What
> > don't I understand???
> >
> >

I assume that:

(1) pl_Resources refers to the first column of pl_Provider,
(2) pl_Provider is set (sorted) in ascending order on pl_provider.

[A] If G8:G14 is guaranteed not to contain any item that does not also
exist pl_Resources, then:

=SUMPRODUCT(--(LOOKUP(G8:G14,pl_Provider)="Yes"),M8:M14)

If Yes/No values are not in the last column of pl_Provider...

=SUMPRODUCT(--(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3))="Yes"),M8:M14)

[B] If G8:G14 might contain items that do not exist in pl_Resources, then:

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G14,pl_Provider)="Yes")),M8:M14)

If Yes/No values are not in the last column of pl_Provider...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3)),M8:M14)


rlutes wrote:
> I could use some help determining my problem with a Sumproduct function.
> I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where
> I'm getting in trouble.
> I have a separate database or multi-row/multi-column array
> (pl_Provider) with the first column a listing of items and in the third
> column of the array I have assigned a value of "Yes" or "No" in the same
> row for each item.
>
> I'm having the formula look at a local range "G8:G14" and then do a
> lookup in the array "pl_Provider" to determine if a Matching record
> from column G exists and if it does to return the value from column 3,
> "Yes" or "No". If it is Yes, I want to include the value in column M
> in the sum.
>
> =SUMPRODUCT(--(INDEX(pl_Provider,
> MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))
>
> The first array in Sumproduct doesn't seem to be giving me an array
> value, but seems to only evaluate it for only the first value. What
> don't I understand???
>
>

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> An #N/A cannot go thru the ISNUMBER tests that figure in the formula.
>
> Here is an example intermediate state of evaluation:
>
> =SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"
> Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

In theory, the above formula should return 4. The problem is that it
returns 0 and I get the following error message...

"Microsoft Excel cannot calculate the formula..."

I understand that the next stages of the evaluation would be as
follows...

=SUMPRODUCT({0;1;1;0;1;0},{0;0;1;0;0;0},{5;6;4;7;8;9})

=SUMPRODUCT({0;0;4;0;0;0})

So why am I getting an error?

I assume that:

(1) pl_Resources refers to the first column of pl_Provider,
(2) pl_Provider is set (sorted) in ascending order on pl_provider.

[A] If G8:G14 is guaranteed not to contain any item that does not also
exist pl_Resources, then:

=SUMPRODUCT(--(LOOKUP(G8:G14,pl_Provider)="Yes"),M8:M14)

If Yes/No values are not in the last column of pl_Provider...

=SUMPRODUCT(--(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3))="Yes"),M8:M14)

[B] If G8:G14 might contain items that do not exist in pl_Resources, then:

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G14,pl_Provider)="Yes")),M8:M14)

If Yes/No values are not in the last column of pl_Provider...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3)),M8:M14)


rlutes wrote:
> I could use some help determining my problem with a Sumproduct function.
> I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where
> I'm getting in trouble.
> I have a separate database or multi-row/multi-column array
> (pl_Provider) with the first column a listing of items and in the third
> column of the array I have assigned a value of "Yes" or "No" in the same
> row for each item.
>
> I'm having the formula look at a local range "G8:G14" and then do a
> lookup in the array "pl_Provider" to determine if a Matching record
> from column G exists and if it does to return the value from column 3,
> "Yes" or "No". If it is Yes, I want to include the value in column M
> in the sum.
>
> =SUMPRODUCT(--(INDEX(pl_Provider,
> MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))
>
> The first array in Sumproduct doesn't seem to be giving me an array
> value, but seems to only evaluate it for only the first value. What
> don't I understand???
>
>

The following formula...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
(G8:G14,pl_Provider)="Yes")),M8:M14)

....seems to fail when any one of the array of lookup values returns a
#N/A value. Therefore, wouldn't the following formula be more
appropriate?

=SUM(IF(ISNUMBER(MATCH(G8:G14,pl_Resources,0)),(LOOKUP(G8:G14,pl_Provider
)="Yes")* M8:M14))

....confirmed with CONTROL+SHIFT+ENTER.

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> I assume that:
>
> (1) pl_Resources refers to the first column of pl_Provider,
> (2) pl_Provider is set (sorted) in ascending order on pl_provider.
>
> [A] If G8:G14 is guaranteed not to contain any item that does not also
> exist pl_Resources, then:
>
> =SUMPRODUCT(--(LOOKUP(G8:G14,pl_Provider)="Yes"),M8:M14)
>
> If Yes/No values are not in the last column of pl_Provider...
>
> =SUMPRODUCT(--(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3))="Yes"),M8:M1
> 4)
>
> [B] If G8:G14 might contain items that do not exist in pl_Resources, then:
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G
> 14,pl_Provider)="Yes")),M8:M14)
>
> If Yes/No values are not in the last column of pl_Provider...
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G
> 14,pl_Resources,INDEX(pl_Provider,0,3)),M8:M14)
>
>
> rlutes wrote:
> > I could use some help determining my problem with a Sumproduct function.
> > I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where
> > I'm getting in trouble.
> > I have a separate database or multi-row/multi-column array
> > (pl_Provider) with the first column a listing of items and in the third
> > column of the array I have assigned a value of "Yes" or "No" in the same
> > row for each item.
> >
> > I'm having the formula look at a local range "G8:G14" and then do a
> > lookup in the array "pl_Provider" to determine if a Matching record
> > from column G exists and if it does to return the value from column 3,
> > "Yes" or "No". If it is Yes, I want to include the value in column M
> > in the sum.
> >
> > =SUMPRODUCT(--(INDEX(pl_Provider,
> > MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))
> >
> > The first array in Sumproduct doesn't seem to be giving me an array
> > value, but seems to only evaluate it for only the first value. What
> > don't I understand???
> >
> >

Domenic wrote:
> The following formula...
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
> (G8:G14,pl_Provider)="Yes")),M8:M14)
>
> ...seems to fail when any one of the array of lookup values returns a
> #N/A value.

[...]

An #N/A cannot go thru the ISNUMBER tests that figure in the formula.

Here is an example intermediate state of evaluation:

=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> An #N/A cannot go thru the ISNUMBER tests that figure in the formula.
>
> Here is an example intermediate state of evaluation:
>
> =SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"
> Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

In theory, the above formula should return 4. The problem is that it
returns 0 and I get the following error message...

"Microsoft Excel cannot calculate the formula..."

I understand that the next stages of the evaluation would be as
follows...

=SUMPRODUCT({0;1;1;0;1;0},{0;0;1;0;0;0},{5;6;4;7;8;9})

=SUMPRODUCT({0;0;4;0;0;0})

So why am I getting an error?

Domenic wrote:
> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
>
>>An #N/A cannot go thru the ISNUMBER tests that figure in the formula.
>>
>>Here is an example intermediate state of evaluation:
>>
>>=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"
>>Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})
>
>
> In theory, the above formula should return 4. The problem is that it
> returns 0 and I get the following error message...
>
> "Microsoft Excel cannot calculate the formula..."
>
> I understand that the next stages of the evaluation would be as
> follows...
>
> =SUMPRODUCT({0;1;1;0;1;0},{0;0;1;0;0;0},{5;6;4;7;8;9})
>
> =SUMPRODUCT({0;0;4;0;0;0})
>
> So why am I getting an error?

No idea. I myself get 4. Want me to send you the workbook?

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> No idea. I myself get 4. Want me to send you the workbook?

Yes please. I'd appreciate it.

Thanks Aladin!

When I opened the workbook, the correct result was shown. But when I
re-entered the formula or clicked in the formula bar and then pressed
ENTER, I got a 0 along with the same error message.

So something must be wrong with my version of Excel. Maybe a bug of
some sort, who knows. I'll have to bring this to Microsoft's attention.

Aladin, thank you very much for your help! Much appreciated!

In article <[email protected]>,
Domenic <[email protected]> wrote:

> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
> > No idea. I myself get 4. Want me to send you the workbook?
>
> Yes please. I'd appreciate it.
>
> Thanks Aladin!

Domenic wrote:
> The following formula...
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
> (G8:G14,pl_Provider)="Yes")),M8:M14)
>
> ...seems to fail when any one of the array of lookup values returns a
> #N/A value.

[...]

An #N/A cannot go thru the ISNUMBER tests that figure in the formula.

Here is an example intermediate state of evaluation:

=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

When I opened the workbook, the correct result was shown. But when I
re-entered the formula or clicked in the formula bar and then pressed
ENTER, I got a 0 along with the same error message.

So something must be wrong with my version of Excel. Maybe a bug of
some sort, who knows. I'll have to bring this to Microsoft's attention.

Aladin, thank you very much for your help! Much appreciated!

In article <[email protected]>,
Domenic <[email protected]> wrote:

> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
> > No idea. I myself get 4. Want me to send you the workbook?
>
> Yes please. I'd appreciate it.
>
> Thanks Aladin!

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> No idea. I myself get 4. Want me to send you the workbook?

Yes please. I'd appreciate it.

Thanks Aladin!

Domenic wrote:
> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
>
>>An #N/A cannot go thru the ISNUMBER tests that figure in the formula.
>>
>>Here is an example intermediate state of evaluation:
>>
>>=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"
>>Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})
>
>
> In theory, the above formula should return 4. The problem is that it
> returns 0 and I get the following error message...
>
> "Microsoft Excel cannot calculate the formula..."
>
> I understand that the next stages of the evaluation would be as
> follows...
>
> =SUMPRODUCT({0;1;1;0;1;0},{0;0;1;0;0;0},{5;6;4;7;8;9})
>
> =SUMPRODUCT({0;0;4;0;0;0})
>
> So why am I getting an error?

No idea. I myself get 4. Want me to send you the workbook?

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> An #N/A cannot go thru the ISNUMBER tests that figure in the formula.
>
> Here is an example intermediate state of evaluation:
>
> =SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"
> Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

In theory, the above formula should return 4. The problem is that it
returns 0 and I get the following error message...

"Microsoft Excel cannot calculate the formula..."

I understand that the next stages of the evaluation would be as
follows...

=SUMPRODUCT({0;1;1;0;1;0},{0;0;1;0;0;0},{5;6;4;7;8;9})

=SUMPRODUCT({0;0;4;0;0;0})

So why am I getting an error?

The following formula...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
(G8:G14,pl_Provider)="Yes")),M8:M14)

....seems to fail when any one of the array of lookup values returns a
#N/A value. Therefore, wouldn't the following formula be more
appropriate?

=SUM(IF(ISNUMBER(MATCH(G8:G14,pl_Resources,0)),(LOOKUP(G8:G14,pl_Provider
)="Yes")* M8:M14))

....confirmed with CONTROL+SHIFT+ENTER.

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> I assume that:
>
> (1) pl_Resources refers to the first column of pl_Provider,
> (2) pl_Provider is set (sorted) in ascending order on pl_provider.
>
> [A] If G8:G14 is guaranteed not to contain any item that does not also
> exist pl_Resources, then:
>
> =SUMPRODUCT(--(LOOKUP(G8:G14,pl_Provider)="Yes"),M8:M14)
>
> If Yes/No values are not in the last column of pl_Provider...
>
> =SUMPRODUCT(--(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3))="Yes"),M8:M1
> 4)
>
> [B] If G8:G14 might contain items that do not exist in pl_Resources, then:
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G
> 14,pl_Provider)="Yes")),M8:M14)
>
> If Yes/No values are not in the last column of pl_Provider...
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G
> 14,pl_Resources,INDEX(pl_Provider,0,3)),M8:M14)
>
>
> rlutes wrote:
> > I could use some help determining my problem with a Sumproduct function.
> > I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where
> > I'm getting in trouble.
> > I have a separate database or multi-row/multi-column array
> > (pl_Provider) with the first column a listing of items and in the third
> > column of the array I have assigned a value of "Yes" or "No" in the same
> > row for each item.
> >
> > I'm having the formula look at a local range "G8:G14" and then do a
> > lookup in the array "pl_Provider" to determine if a Matching record
> > from column G exists and if it does to return the value from column 3,
> > "Yes" or "No". If it is Yes, I want to include the value in column M
> > in the sum.
> >
> > =SUMPRODUCT(--(INDEX(pl_Provider,
> > MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))
> >
> > The first array in Sumproduct doesn't seem to be giving me an array
> > value, but seems to only evaluate it for only the first value. What
> > don't I understand???
> >
> >

I assume that:

(1) pl_Resources refers to the first column of pl_Provider,
(2) pl_Provider is set (sorted) in ascending order on pl_provider.

[A] If G8:G14 is guaranteed not to contain any item that does not also
exist pl_Resources, then:

=SUMPRODUCT(--(LOOKUP(G8:G14,pl_Provider)="Yes"),M8:M14)

If Yes/No values are not in the last column of pl_Provider...

=SUMPRODUCT(--(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3))="Yes"),M8:M14)

[B] If G8:G14 might contain items that do not exist in pl_Resources, then:

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G14,pl_Provider)="Yes")),M8:M14)

If Yes/No values are not in the last column of pl_Provider...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3)),M8:M14)


rlutes wrote:
> I could use some help determining my problem with a Sumproduct function.
> I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where
> I'm getting in trouble.
> I have a separate database or multi-row/multi-column array
> (pl_Provider) with the first column a listing of items and in the third
> column of the array I have assigned a value of "Yes" or "No" in the same
> row for each item.
>
> I'm having the formula look at a local range "G8:G14" and then do a
> lookup in the array "pl_Provider" to determine if a Matching record
> from column G exists and if it does to return the value from column 3,
> "Yes" or "No". If it is Yes, I want to include the value in column M
> in the sum.
>
> =SUMPRODUCT(--(INDEX(pl_Provider,
> MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))
>
> The first array in Sumproduct doesn't seem to be giving me an array
> value, but seems to only evaluate it for only the first value. What
> don't I understand???
>
>

I assume that:

(1) pl_Resources refers to the first column of pl_Provider,
(2) pl_Provider is set (sorted) in ascending order on pl_provider.

[A] If G8:G14 is guaranteed not to contain any item that does not also
exist pl_Resources, then:

=SUMPRODUCT(--(LOOKUP(G8:G14,pl_Provider)="Yes"),M8:M14)

If Yes/No values are not in the last column of pl_Provider...

=SUMPRODUCT(--(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3))="Yes"),M8:M14)

[B] If G8:G14 might contain items that do not exist in pl_Resources, then:

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G14,pl_Provider)="Yes")),M8:M14)

If Yes/No values are not in the last column of pl_Provider...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3)),M8:M14)


rlutes wrote:
> I could use some help determining my problem with a Sumproduct function.
> I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where
> I'm getting in trouble.
> I have a separate database or multi-row/multi-column array
> (pl_Provider) with the first column a listing of items and in the third
> column of the array I have assigned a value of "Yes" or "No" in the same
> row for each item.
>
> I'm having the formula look at a local range "G8:G14" and then do a
> lookup in the array "pl_Provider" to determine if a Matching record
> from column G exists and if it does to return the value from column 3,
> "Yes" or "No". If it is Yes, I want to include the value in column M
> in the sum.
>
> =SUMPRODUCT(--(INDEX(pl_Provider,
> MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))
>
> The first array in Sumproduct doesn't seem to be giving me an array
> value, but seems to only evaluate it for only the first value. What
> don't I understand???
>
>

The following formula...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
(G8:G14,pl_Provider)="Yes")),M8:M14)

....seems to fail when any one of the array of lookup values returns a
#N/A value. Therefore, wouldn't the following formula be more
appropriate?

=SUM(IF(ISNUMBER(MATCH(G8:G14,pl_Resources,0)),(LOOKUP(G8:G14,pl_Provider
)="Yes")* M8:M14))

....confirmed with CONTROL+SHIFT+ENTER.

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> I assume that:
>
> (1) pl_Resources refers to the first column of pl_Provider,
> (2) pl_Provider is set (sorted) in ascending order on pl_provider.
>
> [A] If G8:G14 is guaranteed not to contain any item that does not also
> exist pl_Resources, then:
>
> =SUMPRODUCT(--(LOOKUP(G8:G14,pl_Provider)="Yes"),M8:M14)
>
> If Yes/No values are not in the last column of pl_Provider...
>
> =SUMPRODUCT(--(LOOKUP(G8:G14,pl_Resources,INDEX(pl_Provider,0,3))="Yes"),M8:M1
> 4)
>
> [B] If G8:G14 might contain items that do not exist in pl_Resources, then:
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G
> 14,pl_Provider)="Yes")),M8:M14)
>
> If Yes/No values are not in the last column of pl_Provider...
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP(G8:G
> 14,pl_Resources,INDEX(pl_Provider,0,3)),M8:M14)
>
>
> rlutes wrote:
> > I could use some help determining my problem with a Sumproduct function.
> > I'm wanting to sum a list "M8:M14" if a criteria is met. Here's where
> > I'm getting in trouble.
> > I have a separate database or multi-row/multi-column array
> > (pl_Provider) with the first column a listing of items and in the third
> > column of the array I have assigned a value of "Yes" or "No" in the same
> > row for each item.
> >
> > I'm having the formula look at a local range "G8:G14" and then do a
> > lookup in the array "pl_Provider" to determine if a Matching record
> > from column G exists and if it does to return the value from column 3,
> > "Yes" or "No". If it is Yes, I want to include the value in column M
> > in the sum.
> >
> > =SUMPRODUCT(--(INDEX(pl_Provider,
> > MATCH(G8:G14,pl_Resources,0),3)="Yes"),(M8:M14))
> >
> > The first array in Sumproduct doesn't seem to be giving me an array
> > value, but seems to only evaluate it for only the first value. What
> > don't I understand???
> >
> >

Domenic wrote:
> The following formula...
>
> =SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
> (G8:G14,pl_Provider)="Yes")),M8:M14)
>
> ...seems to fail when any one of the array of lookup values returns a
> #N/A value.

[...]

An #N/A cannot go thru the ISNUMBER tests that figure in the formula.

Here is an example intermediate state of evaluation:

=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> An #N/A cannot go thru the ISNUMBER tests that figure in the formula.
>
> Here is an example intermediate state of evaluation:
>
> =SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"
> Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

In theory, the above formula should return 4. The problem is that it
returns 0 and I get the following error message...

"Microsoft Excel cannot calculate the formula..."

I understand that the next stages of the evaluation would be as
follows...

=SUMPRODUCT({0;1;1;0;1;0},{0;0;1;0;0;0},{5;6;4;7;8;9})

=SUMPRODUCT({0;0;4;0;0;0})

So why am I getting an error?

Domenic wrote:
> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
>
>>An #N/A cannot go thru the ISNUMBER tests that figure in the formula.
>>
>>Here is an example intermediate state of evaluation:
>>
>>=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"
>>Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})
>
>
> In theory, the above formula should return 4. The problem is that it
> returns 0 and I get the following error message...
>
> "Microsoft Excel cannot calculate the formula..."
>
> I understand that the next stages of the evaluation would be as
> follows...
>
> =SUMPRODUCT({0;1;1;0;1;0},{0;0;1;0;0;0},{5;6;4;7;8;9})
>
> =SUMPRODUCT({0;0;4;0;0;0})
>
> So why am I getting an error?

No idea. I myself get 4. Want me to send you the workbook?

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> No idea. I myself get 4. Want me to send you the workbook?

Yes please. I'd appreciate it.

Thanks Aladin!

When I opened the workbook, the correct result was shown. But when I
re-entered the formula or clicked in the formula bar and then pressed
ENTER, I got a 0 along with the same error message.

So something must be wrong with my version of Excel. Maybe a bug of
some sort, who knows. I'll have to bring this to Microsoft's attention.

Aladin, thank you very much for your help! Much appreciated!

In article <[email protected]>,
Domenic <[email protected]> wrote:

> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
> > No idea. I myself get 4. Want me to send you the workbook?
>
> Yes please. I'd appreciate it.
>
> Thanks Aladin!