Quick SUMIF() query - criteria relating to cell

[Matt Knight]

Using Excel 2007

okay, so I'm wanting to SUMIF a range of data in a row if that data
exceeds the value the cell at the end of the range. So say I have a
range from A1:A10 containg numbers and I want to sum them if those
values are less than the value in A11. the formula I tried is as
follows: =SUMIF(A1:A10,>A11,A1:A10) and it doesn't like it (and
putting the >A11 in quotes obviously doesn't make any sense either).
Needing to copy this down 63000 rows means I can't just take the value
in cell A11.

(NB I'll probably need to take averages using either AVERAGEIF or
COUNTIF, so I'm hoping the solution is constitent across all three
functions!)

Many thanks in advance
Matt

 

[Forgone]

Try.....

=SUMPRODUCT((A1:A10<A11)*(A1:A10))

or

=SUMIF(A1:A10,"<"&A11,A1:A10)

 

[Matt Knight]

Try.....

=SUMPRODUCT((A1:A10<A11)*(A1:A10))

or

=SUMIF(A1:A10,"<"&A11,A1:A10)

That's worked brilliantly- I knew it'd just be a simple syntax issue
that noone thought to include in "Help"!

Cheers
Matt