Sum Product Problem
 

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.

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

JDT wrote:
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.

On Mon, 03 Jan 2005 02:16:28 +0100, Aladin Akyurek
wrote:

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

JDT wrote:
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.

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

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

Maybe try instead:

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"JDT" wrote in message
...
On Mon, 03 Jan 2005 02:16:28 +0100, Aladin Akyurek
wrote:

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

JDT wrote:
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.

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

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 wrote:
On Mon, 03 Jan 2005 02:16:28 +0100, Aladin Akyurek
wrote:


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


JDT wrote:

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.


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

On Mon, 03 Jan 2005 12:52:59 +0100, Aladin Akyurek
wrote:

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


JDT wrote:
On Mon, 03 Jan 2005 02:16:28 +0100, Aladin Akyurek
wrote:


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


JDT wrote:

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.


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