+ Reply to Thread
Results 1 to 5 of 5

[SOLVED] sumif range to end of data

  1. #1
    Ian Elliott
    Guest

    [SOLVED] sumif range to end of data

    Thanks for any help.
    I use a SUMIF function to sum values based upon a label in another range. Is
    it possible to use a range that implies till the bottom of the data? There is
    something like that in Visual Basic, I believe it is range.XlUp or
    range.XlDown. I change the range not too often, so it is not a big deal, but
    I would like to make it so it is automatic.
    The formula I use now is:
    SUMIF(A$2:A$991,A9,J$2:J$991)
    which is inside:
    =J9/SUMIF(A$2:A$991,A9,J$2:J$991)*IF(A9="BEUL",Adj!X$3,IF(A9="FEUL",Adj!X$4,IF(A9="AUL",Adj!X$5,"ERROR!")))/N9
    I figure maybe there is something like:
    SUMIF(A$2:A$END,A9,J$2,J$END)
    or something like that?
    Thanks for any help.


  2. #2
    Bob Phillips
    Guest

    Re: sumif range to end of data

    Why not just

    SUMIF(A:A,A9,J:J)


    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "Ian Elliott" <IanElliott@discussions.microsoft.com> wrote in message
    news:DBE0DEA0-D5C1-4752-91F6-BB32714A57E8@microsoft.com...
    > Thanks for any help.
    > I use a SUMIF function to sum values based upon a label in another range.

    Is
    > it possible to use a range that implies till the bottom of the data? There

    is
    > something like that in Visual Basic, I believe it is range.XlUp or
    > range.XlDown. I change the range not too often, so it is not a big deal,

    but
    > I would like to make it so it is automatic.
    > The formula I use now is:
    > SUMIF(A$2:A$991,A9,J$2:J$991)
    > which is inside:
    >

    =J9/SUMIF(A$2:A$991,A9,J$2:J$991)*IF(A9="BEUL",Adj!X$3,IF(A9="FEUL",Adj!X$4,
    IF(A9="AUL",Adj!X$5,"ERROR!")))/N9
    > I figure maybe there is something like:
    > SUMIF(A$2:A$END,A9,J$2,J$END)
    > or something like that?
    > Thanks for any help.
    >




  3. #3
    Ian Elliott
    Guest

    Re: sumif range to end of data

    Thanks Bob!


    "Bob Phillips" wrote:

    > Why not just
    >
    > SUMIF(A:A,A9,J:J)
    >
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (replace somewhere in email address with gmail if mailing direct)
    >
    > "Ian Elliott" <IanElliott@discussions.microsoft.com> wrote in message
    > news:DBE0DEA0-D5C1-4752-91F6-BB32714A57E8@microsoft.com...
    > > Thanks for any help.
    > > I use a SUMIF function to sum values based upon a label in another range.

    > Is
    > > it possible to use a range that implies till the bottom of the data? There

    > is
    > > something like that in Visual Basic, I believe it is range.XlUp or
    > > range.XlDown. I change the range not too often, so it is not a big deal,

    > but
    > > I would like to make it so it is automatic.
    > > The formula I use now is:
    > > SUMIF(A$2:A$991,A9,J$2:J$991)
    > > which is inside:
    > >

    > =J9/SUMIF(A$2:A$991,A9,J$2:J$991)*IF(A9="BEUL",Adj!X$3,IF(A9="FEUL",Adj!X$4,
    > IF(A9="AUL",Adj!X$5,"ERROR!")))/N9
    > > I figure maybe there is something like:
    > > SUMIF(A$2:A$END,A9,J$2,J$END)
    > > or something like that?
    > > Thanks for any help.
    > >

    >
    >
    >


  4. #4
    Registered User
    Join Date
    07-14-2006
    Posts
    1

    Smile 2 column range - sumif

    I have 2 columns... A & B that need to match with 2 other columns in another sheet (Sheet2.A & Sheet2.B) before I add the values in column C. I did SUMIF(A2:B1000;SHEET2.A2:B2;C2:1000) but it gives me a #VALUE error. How should I write my formula?
    I appreciate your help.

  5. #5
    Bob Phillips
    Guest

    Re: sumif range to end of data

    =INDEX(Sheet2!C2:C1000,MATCH(1,(Sheet2!A2:A1000=A2)*(Sheet2!B2:B1000=B2),0))

    which is an array formula, it should be committed with Ctrl-Shift-Enter, not
    just Enter.

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "maricelsy" <maricelsy.2ayuq7_1152932409.6601@excelforum-nospam.com> wrote
    in message news:maricelsy.2ayuq7_1152932409.6601@excelforum-nospam.com...
    >
    > I have 2 columns... A & B that need to match with 2 other columns in
    > another sheet (Sheet2.A & Sheet2.B) before I add the values in column
    > C. I did SUMIF(A2:B1000;SHEET2.A2:B2;C2:1000) but it gives me a #VALUE
    > error. How should I write my formula?
    > I appreciate your help.
    >
    >
    > --
    > maricelsy
    > ------------------------------------------------------------------------
    > maricelsy's Profile:

    http://www.excelforum.com/member.php...o&userid=36383
    > View this thread: http://www.excelforum.com/showthread...hreadid=561543
    >




+ 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