SUMPRODUCT...using (RANG)<>

  • Thread starter Thread starter FLKulchar
  • Start date Start date
F

FLKulchar

Does...=SUMPRODUCT((A1:A10<>1)*(A1*A10))
assign a matrix array to the first part of the formula...
(A1:A10)<>1 when the cell contains 1 as a 0 (zero) or a
1 (one)??

just like the......ISNA(MATCH( etc., etc. does

If so...How does this work...In other words, what
does "<>" represent within the SUMPRODUCT formula??

Thanks,

Larry
 
Larry

As Dave has already mentioned, <> means less than or
greater than, so not equal to.

=SUMPRODUCT((A1:A10<>1)*(A1*A10))
If the range A1 to a10 is a list of numbers from 1 to 10
the answer is 90. (A1:A10<>1) evaluates to 9 because only
one of the ten numbers is 1.

(A1*A10) evaluates to 10 because 1 * 10 so thecalculation
is
9 * 1 * 10 = 90

Sumproduct is usually used with arrays such as

Cost Qty Cost
12.23 2 24.46
12.58 25 314.5
338.96

Sumproduct can calculat the data immediately if the data
is in A1:c3 use the formula =SUMPRODUCT(A2:A3,B2:B3)

You can also do this calculation with an array formula

=SUM(A2:A3*B2:B3) and enter the formula with
Ctrl + Shift +Enter

ISNA(MATCH( are used to lookup numbers and are complex.
Look at the on-line help for VLookup and HLookup functions
before trying the match.

Match is more versatile as it will find a non-sorted list
where a VLOOKUP list must be sorted.

Regards
Peter
 
<< VLOOKUP list must be sorted>> not quite correct.

NB. only if you are using it with ..... TRUE to find a close rathe
than exact match
 
Back
Top