Sum Product Problem

J

JDT

I want to do a sumproduct calculation, but it won't work because one
of the columns of data has formulas in it as well as blank rows.

I took the formulas out and just used the data and it worked, but with
the formulas in there I get an error message.

Is there any way do this? If you need more info, let me know.

Thanks.
 
A

Aladin Akyurek

What is the SumProduct formula you tried and what is intended to calculate?
 
J

JDT

What is the SumProduct formula you tried and what is intended to calculate?
Click to expand...

=SUMPRODUCT((LEFT(E6:E11,2)="1s")*(I6:I11))

The "E" column has no formulas within it, but the "I" column has
formulas and both columns have blank rows. This formulas works fine
if I just use it up to the blank rows, but if I try and use more data
it doesn't work. I get the #Value! error.
 
M

Max

=SUMPRODUCT((LEFT(E6:E11,2)="1s")*(I6:I11))

Maybe try instead:

=SUMPRODUCT(--(LEFT(E6:E11,2)="1s"),I6:I11)
 
A

Aladin Akyurek

If the formulas in I6:I11 return text values like a blank (i.e., ""),
the multiplication operator in

=SUMPRODUCT((LEFT(E6:E11,2)="1s")*(I6:I11))

would not able to cerce such values into numbers. The native comma
syntax od SumProduct ignores such values. So you need to switch to that
syntax:

=SUMPRODUCT(--(LEFT(E6:E11,2)="1s"),I6:I11)

while the conditional with LEFT must be coerced. That is what the -- bit
does.
 
J

JDT

If the formulas in I6:I11 return text values like a blank (i.e., ""),
the multiplication operator in

=SUMPRODUCT((LEFT(E6:E11,2)="1s")*(I6:I11))

would not able to cerce such values into numbers. The native comma
syntax od SumProduct ignores such values. So you need to switch to that
syntax:

=SUMPRODUCT(--(LEFT(E6:E11,2)="1s"),I6:I11)

while the conditional with LEFT must be coerced. That is what the -- bit
does.
Click to expand...

I don't know how you guys do it, but that solution works perfectly. I
could have spent days fiddling around with it and got nothing but a
bad headache. I'm not saying I understand your explanation, because I
don't, but as long as it works I'm happy.

Thanks again. LOL. You guys really amaze me.

JDT
 
Top