SUMPRODUCT with varying # of rows

Hi,

I a few sets of data that would have varying rows, and I need to get the
summation of products of cullumn B*C. How should I do this in excel? using
sumproduct(B:B,C:C) gives me a num error. I want to be able to have this
dynamically updated, hence, I don't want to do this in a macro(unless a macro
can be automatically executed).

Thanks

Ren

I think the problem you are having is because SUMPRODUCT() does not work
with an entire column. Try
=SUMPRODUCT (B1:B65535,C1:C65535)
or adjust to maximum number of rows you need.

Dave

"Ren" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I a few sets of data that would have varying rows, and I need to get the
> summation of products of cullumn B*C. How should I do this in excel? using
> sumproduct(B:B,C:C) gives me a num error. I want to be able to have this
> dynamically updated, hence, I don't want to do this in a macro(unless a
> macro
> can be automatically executed).
>
> Thanks

I don't understand!

If you're willing to use B:B and C:C, why would you want/need a dynamic
range.

With Sumproduct and all array formulas, entire column references are not
allowed (XL07 will change this), so use:

B1:B65535
which is *1* cell short of the entire column.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Ren" <[email protected]> wrote in message
news:[email protected]...
Hi,

I a few sets of data that would have varying rows, and I need to get the
summation of products of cullumn B*C. How should I do this in excel? using
sumproduct(B:B,C:C) gives me a num error. I want to be able to have this
dynamically updated, hence, I don't want to do this in a macro(unless a
macro
can be automatically executed).

Thanks

Brilliant. You are right. Now it works, and I thought it was because
SUMPRODUCT() doesn't work with empty cells.

Thanks

"Dave" wrote:

> Ren
>
> I think the problem you are having is because SUMPRODUCT() does not work
> with an entire column. Try
> =SUMPRODUCT (B1:B65535,C1:C65535)
> or adjust to maximum number of rows you need.
>
> Dave
>
> "Ren" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I a few sets of data that would have varying rows, and I need to get the
> > summation of products of cullumn B*C. How should I do this in excel? using
> > sumproduct(B:B,C:C) gives me a num error. I want to be able to have this
> > dynamically updated, hence, I don't want to do this in a macro(unless a
> > macro
> > can be automatically executed).
> >
> > Thanks
>
>
>

Sorry. I didn't know that colum references are not allowed with array
formulas, and assumed that it had something to do with the way emtpy cells
are handled by sumproduct(), which is why then thought about doing a dynamic
range.

Yes, changing it to B1: B65000 does solve my problem.

Thanks

"RagDyeR" wrote:

> I don't understand!
>
> If you're willing to use B:B and C:C, why would you want/need a dynamic
> range.
>
> With Sumproduct and all array formulas, entire column references are not
> allowed (XL07 will change this), so use:
>
> B1:B65535
> which is *1* cell short of the entire column.
> --
>
> Regards,
>
> RD
> ----------------------------------------------------------------------------
> -------------------
> Please keep all correspondence within the Group, so all may benefit !
> ----------------------------------------------------------------------------
> -------------------
>
> "Ren" <[email protected]> wrote in message
> news:[email protected]...
> Hi,
>
> I a few sets of data that would have varying rows, and I need to get the
> summation of products of cullumn B*C. How should I do this in excel? using
> sumproduct(B:B,C:C) gives me a num error. I want to be able to have this
> dynamically updated, hence, I don't want to do this in a macro(unless a
> macro
> can be automatically executed).
>
> Thanks
>
>
>

Appreciate for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ren" <[email protected]> wrote in message
news:[email protected]...
> Sorry. I didn't know that colum references are not allowed with array
> formulas, and assumed that it had something to do with the way emtpy cells
> are handled by sumproduct(), which is why then thought about doing a
> dynamic
> range.
>
> Yes, changing it to B1: B65000 does solve my problem.
>
> Thanks
>
> "RagDyeR" wrote:
>
>> I don't understand!
>>
>> If you're willing to use B:B and C:C, why would you want/need a dynamic
>> range.
>>
>> With Sumproduct and all array formulas, entire column references are not
>> allowed (XL07 will change this), so use:
>>
>> B1:B65535
>> which is *1* cell short of the entire column.
>> --
>>
>> Regards,
>>
>> RD
>> ----------------------------------------------------------------------------
>> -------------------
>> Please keep all correspondence within the Group, so all may benefit !
>> ----------------------------------------------------------------------------
>> -------------------
>>
>> "Ren" <[email protected]> wrote in message
>> news:[email protected]...
>> Hi,
>>
>> I a few sets of data that would have varying rows, and I need to get the
>> summation of products of cullumn B*C. How should I do this in excel?
>> using
>> sumproduct(B:B,C:C) gives me a num error. I want to be able to have this
>> dynamically updated, hence, I don't want to do this in a macro(unless a
>> macro
>> can be automatically executed).
>>
>> Thanks
>>
>>
>>