+ Reply to Thread
Results 1 to 5 of 5

How do I use a lookup table to sum values for specific criteria?

  1. #1
    Registered User
    Join Date
    05-07-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    8

    Red face How do I use a lookup table to sum values for specific criteria?

    I have a table which gives details of account number, amount in, amount out and settlement date.

    I am trying to set up a formula which sums the amount going in and amount going out separately for each settlement date. Can I do this with a look up table?

    here is an example:

    Column A Column B Column C Column D
    Account Amount Out Amount In Settlement Date
    4001 -5000 10-May
    4001 -1000 10-May
    4001 -2500 11-May
    4001 -100 11-May
    7700 -250 11-May
    7700 -3500 12-May
    4001 -250 12-May
    4001 100 10-May
    4001 5700 10-May
    7700 200 10-May
    7700 6000 10-May
    4001 1000 10-May
    4001 5000 11-May
    7700 3000 11-May
    4001 500 12-May


    I am trying to get a formula to produce the following result:


    Account Net Settlement Date

    4001 -6000 10-May
    4001 -2600 11-May
    4001 -250 12-May
    4001 6800 10-May
    4001 5000 11-May
    4001 500 12-May
    7700 -250 11-May
    7700 -3500 12-May
    7700 6200 10-May
    7700 3000 11-May

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How do I use a lookup table to sum values for specific criteria?

    I am wondering if a Pivot table summarizing data by Account, then by date won't work for you?

    See attached.

    I created a Calculated Field called Net Amount which is the sum of Amount In and Amount Out...
    Attached Files Attached Files
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    05-07-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    8

    Question Re: How do I use a lookup table to sum values for specific criteria?

    Hi, thank you for you help and suggestion. This looks really good but I need a formula which will automatically look at the details every time fresh account information is entered onto the spreadsheet and summarise them to give me a line for every settlement date & account - the sum of the amounts in and the sum of the amount out (I can't net off the total in and out for each settlement date). For example in my spreadsheet account 7700 should end up with four summarised lines, £250 out on the 11/05, £3500 out on 12/05, £6200 in on 10/05 & £3000 in on 11/05. I just can't figure out how I can use a lookup table to look for account first, then settlement date, then sum amounts >0, then sum amounts <0. The example I attached is a small section of a very large spreadsheet with many accounts that I have to manually summarise - there has to be a better way!
    Thanks again & any assistance is greatly appreciated!

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How do I use a lookup table to sum values for specific criteria?

    Here is another attempt with the Pivot Table.

    I added a helper column in the main database to determine whether groupings of numbers based on your intended logic of date change overs....

    you can change the header title as you desire...

    in E2 enter a 1 to denote first group.

    in E3 enter formula:
    Please Login or Register  to view this content.
    copied down.

    Then in the pivot table, the source data would include this helper column in between the Account and Settlement Date columns.

    You can hide this column in the Pivot table.

    You can refresh the pivot table at will by right-clicking the table and selecting Refresh.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-07-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    8

    Smile Re: How do I use a lookup table to sum values for specific criteria?

    Thank you! I will give this a go with the live spreadsheet and see if I can make it work!

+ 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