1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding Values based GL account numbers

Discussion in 'Ask an Excel Question' started by sureshsonti, Sep 14, 2018.

  1. sureshsonti

    sureshsonti Member

    Messages:
    86
    Hello,
    I am trying to add values from a table based on account numbers. For example I have data like this

    Date Acct Amount
    1/1/2018 1500-00 1500
    1/15/2018 1500-01 2000
    2/15/2018 1500-03 1000

    Output for January
    Acct Amount
    1500-00 + 1500-01 3500

    If I type 1500-00 + 1500-01 in a cell I should get the 3500 value for January. The date criteria should also be considered

    Thanks for your help. I have attached a sample file

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,052
    It isn't clear. Also, 1500-00 + 1500-01 should be 3000, I believe.

    I'd recommend splitting ACCT condition into two cells. But if you need to do it in single cell...

    In J4:
    =SUMIFS($D$4:$D$10,$A$4:$A$10,TRIM(LEFT(SUBSTITUTE(H4," ",REPT(" ",50)),50)))+SUMIFS($D$4:$D$10,$A$4:$A$10,TRIM(RIGHT(SUBSTITUTE(H4," ",REPT(" ",50)),50)))

    Copy to J6.
  3. sureshsonti

    sureshsonti Member

    Messages:
    86
    Mr. Chihiro,
    Thanks for your response.

    The 1500-00, 1500-01 are account numbers that have $1500 and $2000 values. You formula partially worked for me. If I add another GL account I am not getting the correct amount. I have attached an updated sample data file.

    Thanks for your help.

    Attached Files:

    Last edited by a moderator: Sep 15, 2018
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,052
    Not according to your workbook ;)
    upload_2018-9-14_14-6-20.png

    Use another construct then.
    =SUMPRODUCT(ISNUMBER(SEARCH($A$4:$A$10,H4))*($D$4:$D$10))
  5. sureshsonti

    sureshsonti Member

    Messages:
    86
    Sorry I was looking what I typed in the problem. Is it possible to extract values based on date range. This formula seems to be working for me. I am still add more GL accts to test.

    Again really appreciate take your time to find a solution to my problem.

    Thanks again
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,052
    Just add another condition to Sumproduct. If you need to filter based on dates as well.

    Ex:
    =SUMPRODUCT(ISNUMBER(SEARCH($A$4:$A$10,H4))*(MONTH($B$4:$B10)=1)*($D$4:$D$10))

    Where you want Month=1. Or use cell reference in place of 1.
    Thomas Kuriakose likes this.
  7. sureshsonti

    sureshsonti Member

    Messages:
    86
    Thanks for your help. Your formula helped me create an income statement in 20 mins which would have taken me 3 hours to create.

    Thanks again
    Last edited by a moderator: Sep 15, 2018

Share This Page