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)),(LO OKUP(G8:G14,pl_Provider
)="Yes")* M8:M14))
....confirmed with CONTROL+SHIFT+ENTER.
In article ,
Aladin Akyurek 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???
|