Hi, I currently am using an array formula to match up certain values, if they
match, then I pull a Qty value from that row. I want to be able to have one
of those value types come from a list of numbers, I call Ncodes. To simply
how the data looks I've created a short version.

Type Ord# QtrCR Qty NCode
A B C D E
5 ze4 4501 1Q08 10 1117
6 ze3 4502 1Q08 15 1116
7 ze4 4503 1Q08 15 1117
8 ze6 4504 1Q08 12 1119

If my Ncode list looks like this, and I name the range "Ncode" -
Placed in a separate area of the Spreadsheet, the result should show Qty of
40.
AA
10 1116
11 1117

Below is an example of the array statement that would pull 1117 only, but it
doesn't work to name a range in place of the 1117, such as AA10:AA11, or use
a named range of Ncode. I'm looking at possibly have an Ncode list of 7 to 10
numbers. So I'm wanting a TRUE for that portion of the formula if any one of
the Ncode list causes a match.

{=(SUM((IF(ISERROR(FIND("Q08",Buffer!$C$5:$C$8)),0 ,1))*(IF(ISNUMBER(Buffer!$B$5:$B$8),1,0))*(IF(ISER ROR(FIND(1117,Buffer!$E$5:$E$8)),0,1))*Buffer!$D$5 :$D$8)}

Thanks ahead of time for any help. Dean

I'll give it a try :). I think you are trying to summarize the data so that
you sum every instance of the Qty for each NCode in your second list. Try
this:

=sumproduct(--(NCode=AA10),--(right(QtrCR,3)="Q08"),Qty)

Place this in cell AB10 then drag it down, and for each value of NCode you
have listed it will sum all the different Qty that also equal Q08.

Mike


"Dean" wrote:

Hi, I currently am using an array formula to match up certain values, if they
match, then I pull a Qty value from that row. I want to be able to have one
of those value types come from a list of numbers, I call Ncodes. To simply
how the data looks I've created a short version.

Type Ord# QtrCR Qty NCode
A B C D E
5 ze4 4501 1Q08 10 1117
6 ze3 4502 1Q08 15 1116
7 ze4 4503 1Q08 15 1117
8 ze6 4504 1Q08 12 1119

If my Ncode list looks like this, and I name the range "Ncode" -
Placed in a separate area of the Spreadsheet, the result should show Qty of
40.
AA
10 1116
11 1117

Below is an example of the array statement that would pull 1117 only, but it
doesn't work to name a range in place of the 1117, such as AA10:AA11, or use
a named range of Ncode. I'm looking at possibly have an Ncode list of 7 to 10
numbers. So I'm wanting a TRUE for that portion of the formula if any one of
the Ncode list causes a match.

{=(SUM((IF(ISERROR(FIND("Q08",Buffer!$C$5:$C$8)),0 ,1))*(IF(ISNUMBER(Buffer!$B$5:$B$8),1,0))*(IF(ISER ROR(FIND(1117,Buffer!$E$5:$E$8)),0,1))*Buffer!$D$5 :$D$8)}

Thanks ahead of time for any help. Dean

Hi Mike, I also placed this question in the Excel Functions side and got the
answer I needed from Bernie. But this looks good for some future situations,
thanks so much for taking the time to reply. Dean

"mikebres" wrote:

I'll give it a try :). I think you are trying to summarize the data so that
you sum every instance of the Qty for each NCode in your second list. Try
this:

=sumproduct(--(NCode=AA10),--(right(QtrCR,3)="Q08"),Qty)

Place this in cell AB10 then drag it down, and for each value of NCode you
have listed it will sum all the different Qty that also equal Q08.

Mike


"Dean" wrote:

Hi, I currently am using an array formula to match up certain values, if they
match, then I pull a Qty value from that row. I want to be able to have one
of those value types come from a list of numbers, I call Ncodes. To simply
how the data looks I've created a short version.

Type Ord# QtrCR Qty NCode
A B C D E
5 ze4 4501 1Q08 10 1117
6 ze3 4502 1Q08 15 1116
7 ze4 4503 1Q08 15 1117
8 ze6 4504 1Q08 12 1119

If my Ncode list looks like this, and I name the range "Ncode" -
Placed in a separate area of the Spreadsheet, the result should show Qty of
40.
AA
10 1116
11 1117

Below is an example of the array statement that would pull 1117 only, but it
doesn't work to name a range in place of the 1117, such as AA10:AA11, or use
a named range of Ncode. I'm looking at possibly have an Ncode list of 7 to 10
numbers. So I'm wanting a TRUE for that portion of the formula if any one of
the Ncode list causes a match.

{=(SUM((IF(ISERROR(FIND("Q08",Buffer!$C$5:$C$8)),0 ,1))*(IF(ISNUMBER(Buffer!$B$5:$B$8),1,0))*(IF(ISER ROR(FIND(1117,Buffer!$E$5:$E$8)),0,1))*Buffer!$D$5 :$D$8)}

Thanks ahead of time for any help. Dean