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))

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))

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))

What result do you get with the second formula?

The 2nd formula was just meant to highlight the possibility
of seemingly incorrect returns due to col F having calculated values.
An inference, given that OP didn't state this.

For values which look like "6.5" in col F due to formatting,
but which are really underlying eg: 6.49, 6.51
then OP may consider using the 2nd formula.

In my response, I didn't cover possibility of text in the ranges
(it was implicitly assumed)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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))

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

but how about the earlier:
=SUMPRODUCT((F20:F2000=6.5)*(P20:R2000))

Didn't it work for you, too?
and without you having to worry about array-entering, to boot

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Actually, no. That returned a value error.

On Sat, 5 Jan 2008 20:36:15 +0800, "Max" wrote:

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

but how about the earlier:
=SUMPRODUCT((F20:F2000=6.5)*(P20:R2000))

Didn't it work for you, too?
and without you having to worry about array-entering, to boot

Actually, no. That returned a value error.

You've got text or formula returned errors then in the range,
which you didn't exactly reveal in your orig. posting

It would have worked otherwise.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

If that's the case, which I don't believe it is, then why did the
other formula work?

On Sat, 5 Jan 2008 22:46:36 +0800, "Max" wrote:

Actually, no. That returned a value error.

You've got text or formula returned errors then in the range,
which you didn't exactly reveal in your orig. posting

It would have worked otherwise.

If that's the case, which I don't believe it is,

You've got text or formula returned errors then in the range,
which you didn't exactly reveal in your orig. posting
It would have worked otherwise.

Does this convince you that it works? :
http://www.freefilehosting.net/download/3a3j8

.. why did the other formula work?
why doesn't daddy respond to you?

note that I never said daddy's formula didn't work

my question to you is:
why were you using a *sumproduct* formula originally?

Does this convince you that it works? :
http://www.freefilehosting.net/download/3a3j8

well? doesn't the sumproduct work, John?
do we still have a discussion here?