Sum Product Problem

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

 

[Aladin Akyurek]

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

 

[JDT]

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

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

 

[Max]

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

Maybe try instead:

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

 

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

 

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

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