View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
~L ~L is offline
external usenet poster
 
Posts: 177
Default Sumproduct Excluding Array

By itself, it returned 1 (The match with 'other' was NA).
When added in to the expanded formula:
=SUMPRODUCT(--(Data!$B$2:$B$2000)*--(ISNA(MATCH(List!$A$2:$A$10,Data!$A$2:$A$2000,0))) )
It returned NA because A2:A10 is not the same size as the other arrays.
When A2:A10 is expanded, the formula returns a number that is higher than the
expected result (the check for this was to sum the totals on the list page,
then sum the values on the data page, since it is hopefully sorting out
numbers by names, the results should be equal but were not).