View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vezerid vezerid is offline
external usenet poster
 
Posts: 751
Default sumproduct range question

Edward,

The formula:

=((A2:A4=A2)*(B2:B4))

in a single cell will not do much. The other formula,

=SUMPRODUCT((A2:A4=A4))

is almost correct. What it does is it examines every cell in A2:A4
against A4 to see if it is equal or not. When it is equal you get TRUE.
So this formula is like (for your example):

=SUMPRODUCT({FALSE,FALSE,TRUE})

Aggregate functions ignore logical values. However, if you get the
negative of the negative of a logical value, negation coerces T/F into
numbers and -- makes T=1 and F=0. Hence, to count how many times A4
value appears in A2:A4 you need:

=SUMPRODUCT(--(A2:A4=A4)) or
=SUMPRODUCT(0+(A2:A4=A4))

i.e. anything to coerce logical to numeric w/o really changing it. In
your original formula,

=SUMPRODUCT((A2:A4=A2)*(B2:B4))

you are multiplying, hence you are forcing coercion to numeric. If you
had the two components as separate arguments then you would need to
coerce each one individually. The following formula is computationally
equivalent to yours:

=SUMPRODUCT(--(A2:A4=A2),B2:B4)

HTH
Kostis Vezerides