ExcelBanter - sigma function

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - sigma function (https://www.excelbanter.com/excel-worksheet-functions/184853-sigma-function.html)

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:G103). 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.

smerk,

Entered normally:
=SUMPRODUCT((B1:B10=A2)*(E1:E10=B2)*(G1:G103)*((B 1:B10)/C2 -1))

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

HTH,
Bernie
MS Excel MVP

"smerk" wrote in message
...
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:G103). 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.