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

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>

 

[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