Excel Formulas, that only add up specific fields

[jonbrom]

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

[TABLE="width: 665"]
<colgroup><col><col span="8"><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Order No.[/TD]
[TD]Supplier[/TD]
[TD]Invoice No.[/TD]
[TD] Net Amount [/TD]
[TD] VAT [/TD]
[TD] Total [/TD]
[TD] Payment Type [/TD]
[TD] Payment Ref [/TD]
[TD] BS Category [/TD]
[/TR]
[TR]
[TD]12/09/2013[/TD]
[TD][/TD]
[TD]Bishops[/TD]
[TD]1[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Credit Card[/TD]
[TD]22[/TD]
[TD]Accounting[/TD]
[/TR]
[TR]
[TD]12/09/2013[/TD]
[TD][/TD]
[TD]Warehouse[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Stock[/TD]
[/TR]
[TR]
[TD]13/09/2013[/TD]
[TD][/TD]
[TD]Bishops[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Accounting[/TD]
[/TR]
[TR]
[TD]14/09/2013[/TD]
[TD][/TD]
[TD]Warehouse[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Stock[/TD]
[/TR]
[TR]
[TD]15/09/2013[/TD]
[TD][/TD]
[TD]Drawings[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]16/09/2013[/TD]
[TD][/TD]
[TD]Warehouse[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Stock[/TD]
[/TR]
[TR]
[TD]17/09/2013[/TD]
[TD][/TD]
[TD]Warehouse[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Stock[/TD]
[/TR]
[TR]
[TD]18/09/2013[/TD]
[TD][/TD]
[TD]Bishops[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Accounting[/TD]
[/TR]
[TR]
[TD]19/09/2013[/TD]
[TD][/TD]
[TD]Warehouse[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Stock[/TD]
[/TR]
[TR]
[TD]20/09/2013[/TD]
[TD][/TD]
[TD]Drawings[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]21/09/2013[/TD]
[TD][/TD]
[TD]Warehouse[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Stock[/TD]
[/TR]
[TR]
[TD]22/09/2013[/TD]
[TD][/TD]
[TD]Bishops[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Accounting[/TD]
[/TR]
[TR]
[TD]23/09/2013[/TD]
[TD][/TD]
[TD]Drawings[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]24/09/2013[/TD]
[TD][/TD]
[TD]Warehouse[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Stock[/TD]
[/TR]
[TR]
[TD]25/09/2013[/TD]
[TD][/TD]
[TD]Bishops[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Accounting[/TD]
[/TR]
[TR]
[TD]26/09/2013[/TD]
[TD][/TD]
[TD]Drawings[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]27/09/2013[/TD]
[TD][/TD]
[TD]Bishops[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Accounting[/TD]
[/TR]
[TR]
[TD]28/09/2013[/TD]
[TD][/TD]
[TD]Drawings[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]29/09/2013[/TD]
[TD][/TD]
[TD]Drawings[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]30/09/2013[/TD]
[TD][/TD]
[TD]Drawings[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]01/10/2013[/TD]
[TD][/TD]
[TD]Drawings[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]02/10/2013[/TD]
[TD][/TD]
[TD]Warehouse[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Stock[/TD]
[/TR]
[TR]
[TD]03/10/2013[/TD]
[TD][/TD]
[TD]Bishops[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Accounting[/TD]
[/TR]
[TR]
[TD]04/10/2013[/TD]
[TD][/TD]
[TD]Drawings[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]05/10/2013[/TD]
[TD][/TD]
[TD]Bishops[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Accounting[/TD]
[/TR]
[TR]
[TD]06/10/2013[/TD]
[TD][/TD]
[TD]Drawings[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]07/10/2013[/TD]
[TD][/TD]
[TD]Warehouse[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Stock[/TD]
[/TR]
[TR]
[TD]08/10/2013[/TD]
[TD][/TD]
[TD]Drawings[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]09/10/2013[/TD]
[TD][/TD]
[TD]Bishops[/TD]
[TD]2[/TD]
[TD] £ 100.00[/TD]
[TD] £ 20.00[/TD]
[TD] £ 120.00[/TD]
[TD]Cash[/TD]
[TD]11[/TD]
[TD]Accounting[/TD]
[/TR]
</tbody>[/TABLE]



i then need the totals for each of the BS Category (last column) below. first field is C5

[TABLE="width: 552"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Expenditure[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD]BS Allocation[/TD]
[/TR]
[TR]
[TD]Wages & Salaries (Net)[/TD]
[TD] [/TD]
[TD] £ 15,000.00[/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]PAYE & NI[/TD]
[TD] [/TD]
[TD] £ 5,070.00[/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Accounting[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Advertising & promotion[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Architect[/TD]
[TD] [/TD]
[TD] £ - [/TD]
[TD]Land & Buildings[/TD]
[/TR]
[TR]
[TD]Bank charges[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Building Contruction[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Land & Buildings[/TD]
[/TR]
[TR]
[TD]Car Hire[/TD]
[TD] [/TD]
[TD] £ - [/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Courier and Delivery Charges[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Fixtures and furniture[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Fixtures & Fittings[/TD]
[/TR]
[TR]
[TD]Franchise costs[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Freehold/Lease premium[/TD]
[TD] [/TD]
[TD] £ - [/TD]
[TD]Land & Buildings[/TD]
[/TR]
[TR]
[TD]Insurance[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]IT & Software Expenses[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Legal Fees[/TD]
[TD] [/TD]
[TD] £ - [/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Licenses and data[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Motoring costs (fuel, road tax)[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Professional Fees[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Other fixed assets[/TD]
[TD] [/TD]
[TD] £ - [/TD]
[TD]Other Fixed Assets[/TD]
[/TR]
[TR]
[TD]Maintenance[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Rent [/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Stationary, postage, general office[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Stock[/TD]
[TD] [/TD]
[TD] £ - [/TD]
[TD]Stock, WIP & Finished Goods[/TD]
[/TR]
[TR]
[TD]Telephone[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Travel & Subsistence[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Utilities[/TD]
[TD] [/TD]
[TD] £ - [/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD]Vehicles[/TD]
[TD] [/TD]
[TD] £ - [/TD]
[TD]Plant, Machinery & Vehicles[/TD]
[/TR]
[TR]
[TD]Website development[/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[TD]Cash (Bank & Deposits)[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Total[/TD]
[TD]#VALUE![/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Have a look at the SumIf(s) functions(s).

 

[Pup Denab]

Hi Jobbrom

This formula
=SUMIF(J:J,J2,G:G)
J:J is the column for BS Category
C5 is the acc
G:G is the totals column
copy the formula to cover you data starting at F5 I think

 

[Comfy]

Try using a pivot table, much easier:

Excel 2010
	A	B	C	D
11
12
13
14		Data
15	Supplier	Sum of Net Amount	Sum of VAT	Sum of Total
16	Bishops	900	180	1080
17	Drawings	1100	220	1320
18	Warehouse	900	180	1080
19	Grand Total	2900	580	3480
Sheet2

I don't understand what you are asking in the second part.

Is this what you want? again using a pivot table:

Excel 2010
	A	B
20
21
22	Sum of Amount
23	BS Allocation	Total
24	Cash (Bank & Deposits)	20070
25	Fixtures & Fittings	0
26	Land & Buildings	0
27	Other Fixed Assets	0
28	Plant, Machinery & Vehicles	0
29	Stock, WIP & Finished Goods	0
30	Grand Total	20070
31
Sheet2

 

[jonbrom]

that worked, thanks