Hi Bob, thanks for your reply
I've tried to interperate the formula:
=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A26,A30:A35,0))),D2
26)
with only a little bit of success.
(MATCH(A2:A26,A30:A35,0))
According to reading the MS help files this would give a number from
1-6 depending on what matches what
MATCH returns the position of the matched value within lookup_array, not
the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the
relative position of "b" within the array {"a","b","c"}.
but what I actually see is the numbers 1-27 being returned (at least
that's what I think I see)
ISNUMBER checks to see if there is a number or if it equals 'false'.
I have no idea what the '--' signifies
SUMPRODUCT
The following formula multiplies all the components of the two arrays on
the preceding worksheet and then adds the products — that is, 3*2
+ 4*7 + 8*6 + 6*7 + 1*5 + 9*3.
SUMPRODUCT({3,4;8,6;1,9}, {2,7;6,7;5,3}) equals 156
adds the results together if there is a ';' separating the numbers?
D2
26 is part of the SUMPRODUCT array but I'm not sure how the formula
has left out the non-required numbers (I assume it has something to do
with the ISNUMBER = False bit but assumptions are never a good idea)
Am I close?