Formula Help Please

[Chris McNAmee]

Hi,
Hoping someone can help me with formula. I need to total pay components in one column be cost centre but the totals need to appear in a different worksheet I only want to pick up certain pay components though eg salary, Overtime etc Thinking I need an Index Match and Sumif or Sum product? any help would be appreciated.
Thanks,

 

[RasGhul]

Hi,

SUMIFS is usually the best for sum with criteria, have you got some sample data you could post?

 

[Chris McNAmee]

I have some sample data but i'm unable to post the image or attached anything

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>

 

[Chris McNAmee]

	Sheet 1
A	B	C	D	E	F	G	H
Cost Centre	Cost Centre Description	Division Description	ML4 Description	Salary	Overtime & Penalties
				Hours	Amount	Hours	Amount
0000	Corporate	Other	Other
0001	Adelaide Bank Group	Other	Other
0002	Integration Costs	Other	Other

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Chris McNAmee]

Sheet2
A	B	C	D
Cost Centre	Pay Component Code Description	Pay Component Code	Total
0000	Bonus - Superable - Corporate Funded	402.1	$ 621.00
0000	First Aid Allow	411	$ 100.47
0000	Govt Paid Parental Bonus	412	$ 184,541.34
0000	Uniform Repayment	802.1	$ 16,076.31

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Chris McNAmee]

I only want to total specific code by cost centre on sheet 1

 

[RasGhul]

So total of Column D sheet 2 with cost centre code as criteria, where do you want the result to be on sheet 1?

 

[Chris McNAmee]

So total of Column D sheet 2 with cost centre code as criteria, where do you want the result to be on sheet 1?

Correct but only totaling for example code 402.1 for cost centre 0000 to appear on column F on sheet 1

 

[RasGhul]

Can you use this?

Book1
	A	B	C	D	E	F	G	H
1	Pay Component Code
2	402.1
3
4	Cost Centre	Cost Centre Description	Division Description	ML4 Description	Salary		Overtime & Penalties
5					Hours	Amount	Hours	Amount
6	0000	Corporate	Other	Other		$621.00
7	0001	Adelaide Bank Group	Other	Other		$0.00
8	0002	Integration Costs	Other	Other		$0.00
Sheet1

Cell Formulas
Range	Formula
F6	=SUMIFS(Sheet2!$D$2:$D$5,Sheet2!$A$2:$A$5,Sheet1!A6,Sheet2!$C$2:$C$5,$A$2)

 

[Chris McNAmee]

I Think I would need reference the pay component in the formula as different codes are relating to different costs.