SumProduct?

J

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

=SUMPRODUCT((F20:F2000=6.5)*(P20:p2000+Q20:Q2000+r20:r2000))
 
M

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

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
 
M

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

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.
 
M

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
 
J

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
Click to expand...
 
M

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.
 
J

JP

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