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

Howdy. If I understand correctly, you should consider using SUMPRODUCT. That will give you conditional sums for multiple criteria.

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

Thanks for your posts Shades and Fazza.... I'll have a try with those suggestions today. Any further suggestions are still very welcome!