Array advance sum formula

[Guest]

=Sum(((Range1=value1/cell1)+(Range1=value2/cell2))*(Range2)
The above formula works fine it will sum Range2 where values in Range1 are
matching with cell1/value1 OR cell2/value.
My question is what if i have to compare values in Range1 with 10 or more
cells/values. currently it works by repeating the conditions say 10 times,
but this will make formula much bigger. Is there any way Range1 can be
compared with cell1, cell2,...cell10 at the same time.

 

[Lori]

Try using an array constant if it is a list of values:

=Sumproduct((Range1={value1,value2,...})*Range2)

Sumproduct has the advantage that it does not need to be array-
entered.

For cell references, instead of the array you can use a range such as
A1:E1 or transpose(a1:a5) for a vertical range or
Choose({1,2,3},b2,c5,e7) for a discontinuous range.

 

[Bob Phillips]

=SUM((ISNUMBER(MATCH(Range1,cell1:celln,0)))*Range2)

and

=SUM((ISNUMBER(MATCH(Range1,{"value1","value2"},0)))*Range2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bernard Liengme]

I would use SUMPRODUCT
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html
and http://mcgimpsey.com/excel/formulae/doubleneg.html
For example, one can use
=SUMPRODUCT(--(A1:A100=C1:E1)*B1:B100)
to sum B1:B100 when corresponding A values match values in C1:E1 (must be
horizontal range, as I recall)
best wishes