hi
i need some help with a formula, i have been trying all day to figure it out and cant. basically i need to be able to add up all payments for a particular client from a list where there are other payments form other clients. please see below.
any help would be much appreciated.
this is the list of payments which start from field E7
<colgroup><col><col span="8"><col></colgroup><tbody>
</tbody>
i then need the totals for each of the BS Category (last column) below. first field is C5
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
i need some help with a formula, i have been trying all day to figure it out and cant. basically i need to be able to add up all payments for a particular client from a list where there are other payments form other clients. please see below.
any help would be much appreciated.
this is the list of payments which start from field E7
Date | Order No. | Supplier | Invoice No. | Net Amount | VAT | Total | Payment Type | Payment Ref | BS Category |
12/09/2013 | Bishops | 1 | £ 100.00 | £ 20.00 | £ 120.00 | Credit Card | 22 | Accounting | |
12/09/2013 | Warehouse | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Stock | |
13/09/2013 | Bishops | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Accounting | |
14/09/2013 | Warehouse | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Stock | |
15/09/2013 | Drawings | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Cash | |
16/09/2013 | Warehouse | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Stock | |
17/09/2013 | Warehouse | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Stock | |
18/09/2013 | Bishops | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Accounting | |
19/09/2013 | Warehouse | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Stock | |
20/09/2013 | Drawings | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Cash | |
21/09/2013 | Warehouse | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Stock | |
22/09/2013 | Bishops | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Accounting | |
23/09/2013 | Drawings | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Cash | |
24/09/2013 | Warehouse | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Stock | |
25/09/2013 | Bishops | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Accounting | |
26/09/2013 | Drawings | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Cash | |
27/09/2013 | Bishops | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Accounting | |
28/09/2013 | Drawings | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Cash | |
29/09/2013 | Drawings | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Cash | |
30/09/2013 | Drawings | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Cash | |
01/10/2013 | Drawings | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Cash | |
02/10/2013 | Warehouse | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Stock | |
03/10/2013 | Bishops | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Accounting | |
04/10/2013 | Drawings | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Cash | |
05/10/2013 | Bishops | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Accounting | |
06/10/2013 | Drawings | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Cash | |
07/10/2013 | Warehouse | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Stock | |
08/10/2013 | Drawings | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Cash | |
09/10/2013 | Bishops | 2 | £ 100.00 | £ 20.00 | £ 120.00 | Cash | 11 | Accounting |
<colgroup><col><col span="8"><col></colgroup><tbody>
</tbody>
i then need the totals for each of the BS Category (last column) below. first field is C5
Expenditure | 0 | BS Allocation | |
Wages & Salaries (Net) | £ 15,000.00 | Cash (Bank & Deposits) | |
PAYE & NI | £ 5,070.00 | Cash (Bank & Deposits) | |
Accounting | #VALUE! | Cash (Bank & Deposits) | |
Advertising & promotion | #VALUE! | Cash (Bank & Deposits) | |
Architect | £ - | Land & Buildings | |
Bank charges | #VALUE! | Cash (Bank & Deposits) | |
Building Contruction | #VALUE! | Land & Buildings | |
Car Hire | £ - | Cash (Bank & Deposits) | |
Courier and Delivery Charges | #VALUE! | Cash (Bank & Deposits) | |
Fixtures and furniture | #VALUE! | Fixtures & Fittings | |
Franchise costs | #VALUE! | Cash (Bank & Deposits) | |
Freehold/Lease premium | £ - | Land & Buildings | |
Insurance | #VALUE! | Cash (Bank & Deposits) | |
IT & Software Expenses | #VALUE! | Cash (Bank & Deposits) | |
Legal Fees | £ - | Cash (Bank & Deposits) | |
Licenses and data | #VALUE! | Cash (Bank & Deposits) | |
Motoring costs (fuel, road tax) | #VALUE! | Cash (Bank & Deposits) | |
Professional Fees | #VALUE! | Cash (Bank & Deposits) | |
Other fixed assets | £ - | Other Fixed Assets | |
Maintenance | #VALUE! | Cash (Bank & Deposits) | |
Rent | #VALUE! | Cash (Bank & Deposits) | |
Stationary, postage, general office | #VALUE! | Cash (Bank & Deposits) | |
Stock | £ - | Stock, WIP & Finished Goods | |
Telephone | #VALUE! | Cash (Bank & Deposits) | |
Travel & Subsistence | #VALUE! | Cash (Bank & Deposits) | |
Utilities | £ - | Cash (Bank & Deposits) | |
Vehicles | £ - | Plant, Machinery & Vehicles | |
Website development | #VALUE! | Cash (Bank & Deposits) | |
Total | #VALUE! |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>