Results 1 to 4 of 4

Thread: Excel 2007 - Sum of value found in Column BT if value in Column D equal the criteria

  1. #1

    Excel 2007 - Sum of value found in Column BT if value in Column D equal the criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I tried the following formula but getting an error due to some of the rows are blank "Circular Reference"
    HTML Code:
    =SUMIF($D:$D,"="&INDIRECT("$D"&ROW()+1),BT:BT)
    Is there a way to get the last row within a column and get the formula to work accordingly?

    Your help is appreciated.

    Regards,

    Chuck

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Where exactly are you entering this formula?

    What exactly are you expecting the formula to do?


  3. #3
    Hi,

    Thanks for replying.

    I have this formula in column BT and have from BT6 to n number of rows values and in Column D I have a contract number that can be repeated n number of times. So my objective is to be able sum up o\all the values related to that particular contract number and have the sum added in the Cell BT5.

    Each time a new contract is listed a line is dedicated to sum up the value corresponding to that contract as the first listed row .

    e,g:

    Column Column
    D BT
    Contract # Total
    123456789 $500
    123456789 $100
    123456789 $100
    123456789 $100
    123456789 $100
    123456789 $100

    234567890 $250
    234567890 $125
    234567890 $125

    Regards,

    Chuck

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Try in BT5:

    Code:
    =SUM(BT6:INDEX(BT:BT,ROW()+COUNTIF(D:D,D5)-1))
    you can then copy that cell to BT12 (the start of the next batch).
    Last edited by NBVC; 2013-12-03 at 09:14 PM.


  5. #5

    Excel 2007 - Sum of value found in Column BT if value in Column D equal the criteria

    Thanks NBVC!

    It works like a charm.

    Regards,

    Chuck

Posting Permissions

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