SumProduct?

[JP]

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)*(P202000+Q20:Q2000))

=SUMPRODUCT((F20:F2000=6.5)*(P202000+Q20:Q2000+r20:r2000))

 

[Max]

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

 

[daddylonglegs]

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]

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)

---

 

[JP]

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.

 

[Max]

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

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


---

 

[JP]

Actually, no. That returned a value error.

but how about the earlier:

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

 

[Max]

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.

---

 

[JP]

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

 

[Max]

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

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?

 

[Max]

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?