Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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??? -- rlutes ------------------------------------------------------------------------ rlutes's Profile: http://www.excelforum.com/member.php...o&userid=24038 View this thread: http://www.excelforum.com/showthread...hreadid=376480 |
#2
|
|||
|
|||
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??? |
#3
|
|||
|
|||
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! -- rlutes ------------------------------------------------------------------------ rlutes's Profile: http://www.excelforum.com/member.php...o&userid=24038 View this thread: http://www.excelforum.com/showthread...hreadid=376480 |
#4
|
|||
|
|||
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??? |
#5
|
|||
|
|||
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}) |
#6
|
|||
|
|||
In article ,
Aladin Akyurek 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? |
#7
|
|||
|
|||
Domenic wrote:
In article , Aladin Akyurek 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? |
#8
|
|||
|
|||
In article ,
Aladin Akyurek wrote: No idea. I myself get 4. Want me to send you the workbook? Yes please. I'd appreciate it. :) Thanks Aladin! |
#9
|
|||
|
|||
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 , Domenic wrote: In article , Aladin Akyurek wrote: No idea. I myself get 4. Want me to send you the workbook? Yes please. I'd appreciate it. :) Thanks Aladin! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|