thanks-almost working! but, the SUM formula isn't what I wanted.
i want to do

sum((x/C2)-1), where x = B1, B2, B3, B4, B5, B6, B7, B8, B9 and B10
BUT also, so that only those data that pass the criteria
(B1:B10=A2)*(E1:E10=B2)*(G1:G10A3) are included in the calculation. Is this
possible? thanks for any suggestions

"Bernard Liengme" wrote:

Did you try it, to see if it works?
I used this:
=IF((B1:B10=A2)*(E1:E10=B2)*(G1:G10A3), SUM((((B1:B10)/C2)-1)),"ZZZZ")
as an array function, and it worked.
Tell us what happened when you tried it.
This will also work, if you are OK with O when the conditions are not all
meet.
=(B1:B10=A2)*(E1:E10=B2)*(G1:G10A3)* SUM((((B1:B10)/C2)-1))
(array entered)

But David's =(SUM(B1:B10)/C2)-COUNT(B1:B10) makes more sence.

Please note you do not need your opening SUM. So for expample
while =SUM(A1*A2) will give the correct answer, so will =A1*A2
best wishes

--
www.stfx.ca/people/bliengme


"smerk" wrote:

thanks. now is it possible to use this formula within another array function?
What I would like to do, is have this formula

=SUM((((B1:B10)/C2)-1)

as the 'true' value in an IF formula

as in
=SUM(IF((B1:B10=A2)*(E1:E10=B2)*(G1:G10A3), TRUE VALUE, FALSE VALUE))

thanks

"Gary''s Student" wrote:

=SUM((((B1:B10)/C2)-1))

but it must be entered as an array function with CNTRL-SHFT-ENTER rather
than just ENTER.
--
Gary''s Student - gsnu200779


"smerk" wrote:

eg. that could complete a function such as

sum of (((B1:B10)/C2)-1)