Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2006
    Posts
    2

    Red face Unanswered: Sumif And Lookup?

    Hi,

    I'm struggling to find a suitable MS Excel formula for an accounts spreadsheet (see attached Accounts.bmp). The problem is that for cells J4:K20 and for subsequent months. Each of these cells should include a formula which looks for the rows in the left hand table associated with the month in question, then in that range of rows looks for the rows where item=code and then returns the 'Amount In' minus the 'Amount Out'. Sorry for not being able to explain better what I am requiring but I hope you get the idea.

    Any help would be greatly appreciated!

    Thanks, Mark
    Attached Thumbnails Attached Thumbnails accounts.bmp  

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. If I understand correctly, you should consider using SUMPRODUCT. That will give you conditional sums for multiple criteria.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2006
    Posts
    113
    Another approach might be with a pivot table. These are great for such tasks. It could replace the monthly totals table. Maybe better on a separate sheet.

    Or, you could leave your current table and use GETPIVOTDATA formulae to extract the results from a pivot table. So the pivot table might be on another worksheet and your current summary links to it.

    Another good approach would be to use SUMIF formulae and define the correct range by using a combination of OFFSET, INDEX & COUNT. For example for month K3 = October, use INDEX to find the first instance of October in column A and COUNT to know how many. Then OFFSET from A3 to define the particular range. Be careful with this approach to make sure the year is the correct one if the data extends across more than one year.

    Or, less simply, you could use a query table with a TRANSFORM crosstab summation.

    I guess there are many ways.

    HTH
    Fazza

  4. #4
    Join Date
    Oct 2006
    Posts
    2
    Thanks for your posts Shades and Fazza.... I'll have a try with those suggestions today. Any further suggestions are still very welcome!

Posting Permissions

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