A slightly complicated finance problem
 

At school I have recieved a project where I have several seperate
workbooks with invoices for different products. These products have a
purchase fee and an on-going subscription so there are invoices for
different ranges which are payed over several months.

I have to create a summary of all the data, making a table of all the
different companies and their total(and product by product) invoices
per financial year.
The data is laid out as follows:

Microsoft -------- Apple --------- Intel
£51,205 -------- £23,504 --------- £82,512
21/3/2002 ------- 15/7/2003 -------- 5/6/2002
£13,214 ------- £34,312 -------- £9,312
15/1/2003 ------- 9/10/2003 -------- 2/8/2002




I started by trying to do it by brute force, i.e:
=IF(AND('[WB1]Invoices'!C1037712,'[WB1]Invoices'!C10<38078),'[WB1]
Invoices'!C9,"0")
37712 being the serial date for April 1 2003 and 38078 April 1 2004
But as I go through the list of tens and tens of different invoices I
end up with huge lists functions in a single cell and I lose track of
where I am.

My question is if there is a more efficient way to do this?

Thanks in advance for any help

A slightly complicated finance problem
 

You might consider a pivot table, which lends itself very well to financial
summaries by client, product, date, etc. Pivot tables even have built-in
tools to summarize daily, monthly, quarterly, or annually for you.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"TopCat" wrote:

At school I have recieved a project where I have several seperate
workbooks with invoices for different products. These products have a
purchase fee and an on-going subscription so there are invoices for
different ranges which are payed over several months.

I have to create a summary of all the data, making a table of all the
different companies and their total(and product by product) invoices
per financial year.
The data is laid out as follows:

Microsoft -------- Apple --------- Intel
£51,205 -------- £23,504 --------- £82,512
21/3/2002 ------- 15/7/2003 -------- 5/6/2002
£13,214 ------- £34,312 -------- £9,312
15/1/2003 ------- 9/10/2003 -------- 2/8/2002




I started by trying to do it by brute force, i.e:
=IF(AND('[WB1]Invoices'!C1037712,'[WB1]Invoices'!C10<38078),'[WB1]
Invoices'!C9,"0")
37712 being the serial date for April 1 2003 and 38078 April 1 2004
But as I go through the list of tens and tens of different invoices I
end up with huge lists functions in a single cell and I lose track of
where I am.

My question is if there is a more efficient way to do this?

Thanks in advance for any help