sigma function

[smerk]

I want to be able to

sum((x/C2)-1), where x = B1, B2, B3, B4, B5, B6, B7, B8, B9 and B10

In addition, I would like to include only those values (B1 to B10) that pass
certain criteria (using an array formula), such as
(B1:B10=A2)*(E1:E10=B2)*(G1:G10>3). I have the array function working, but
haven't managed to incorporate the sum function correctly-excel simply uses
all the values, rather than only those that pass the criteria.

 

[Bernie Deitrick]

smerk,

Entered normally:
=SUMPRODUCT((B1:B10=A2)*(E1:E10=B2)*(G1:G10>3)*((B1:B10)/C2 -1))

Or array entered:
=SUM((B1:B10=A2)*(E1:E10=B2)*(G1:G10>3)*((B1:B10)/C2 -1))

HTH,
Bernie
MS Excel MVP