Sum
Thanks a lot
"Max" wrote:
MATCH(A1:A10,C1:C2,0)
exact matches every element in A1:A10 with that in C1:C2
and returns a resulting col array like this:
{#N/A;1;#N/A;2;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A}
where #N/A = no match,
numbers 1, 2 = the relative positions where the match is found
ISNUMBER(MATCH(A1:A10,C1:C2,0))
then converts it to True/False:
{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FAL SE;FALSE}
where #N/A = False, any number (ie the 1, 2) = True
The double minus: --
--(ISNUMBER(MATCH(...)))
then converts the True/Falses to 1/0:
{0;1;0;1;0;0;0;0;0;0}
The sumproduct then cross-multiplies the above final array of 1/0s with the
corresponding numbers in B1:B10 and sums the lot, giving the desired result
--
Max
Singapore
"Rod" wrote in message
...
Thank you so much
If you don't mind could you please explain briefly the logic behind it?
Thanks in advance.
.
|