Thanks to all. I used =sum(if(f20:f2000=6.5,P20:r2000)) It did the
job.

I have one question. How do you know when something is to be an array
formula. Does that occur when you are querying a range?

Thank you.

On Fri, 4 Jan 2008 17:48:06 -0800, daddylonglegs
wrote:

What result do you get with the second formula? If there's any text in
R20:R2000 (that can't be co-erced to a number) then your formula will give a
#VALUE! error.

This formula will ignore any text

=SUM(IF(F20:F2000=6.5,P20:R2000))

It's an "array formula" which must be confirmed with CTRL+SHIFT+ENTER so
that curly braces like { and } appear around the formula in the formula bar



"Max" wrote:

Provided there are no error values within the ranges concerned
you could try it as:
=SUMPRODUCT((F20:F2000=6.5)*(P20:R2000))

Alternatively, if seemingly incorrect results are being returned
due to col F containing calculated values,
then perhaps you could use round:
=SUMPRODUCT((ROUND(F20:F2000,1)=6.5)*(P20:R2000))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JP" wrote in message
...
The following formula will sum two columns if th criteria is met in
the first column, but it won't sum three coumns. Any suggestions?

=SUMPRODUCT((F20:F2000=6.5)*(P20:P2000+Q20:Q2000))

=SUMPRODUCT((F20:F2000=6.5)*(P20:P2000+Q20:Q2000+r 20:r2000))