+ Reply to Thread
Results 1 to 6 of 6

SUMPRODUCT with varying # of rows

  1. #1
    Ren
    Guest

    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

  2. #2
    Dave
    Guest

    Re: SUMPRODUCT with varying # of rows

    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" <Ren@discussions.microsoft.com> wrote in message
    news:80964604-D84F-420B-9E17-F2C43DACCD13@microsoft.com...
    > 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




  3. #3
    RagDyeR
    Guest

    Re: SUMPRODUCT with varying # of rows

    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" <Ren@discussions.microsoft.com> wrote in message
    news:80964604-D84F-420B-9E17-F2C43DACCD13@microsoft.com...
    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



  4. #4
    Ren
    Guest

    Re: SUMPRODUCT with varying # of rows

    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" <Ren@discussions.microsoft.com> wrote in message
    > news:80964604-D84F-420B-9E17-F2C43DACCD13@microsoft.com...
    > > 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

    >
    >
    >


  5. #5
    Ren
    Guest

    Re: SUMPRODUCT with varying # of rows

    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" <Ren@discussions.microsoft.com> wrote in message
    > news:80964604-D84F-420B-9E17-F2C43DACCD13@microsoft.com...
    > 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
    >
    >
    >


  6. #6
    Ragdyer
    Guest

    Re: SUMPRODUCT with varying # of rows

    Appreciate for the feed-back.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "Ren" <Ren@discussions.microsoft.com> wrote in message
    news:2AE0160E-5CB7-4934-9525-169E9D302455@microsoft.com...
    > 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" <Ren@discussions.microsoft.com> wrote in message
    >> news:80964604-D84F-420B-9E17-F2C43DACCD13@microsoft.com...
    >> 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
    >>
    >>
    >>



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1