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
[TABLE="width: 1263"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>[/TABLE]

 

[Chris McNAmee]

[TABLE="width: 1263"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Sheet 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]Cost Centre[/TD]
[TD]Cost Centre Description[/TD]
[TD]Division Description[/TD]
[TD]ML4 Description[/TD]
[TD="colspan: 2"]Salary[/TD]
[TD="colspan: 2"]Overtime & Penalties[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Hours[/TD]
[TD]Amount[/TD]
[TD]Hours[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]0000[/TD]
[TD]Corporate[/TD]
[TD]Other[/TD]
[TD]Other[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]Adelaide Bank Group[/TD]
[TD]Other[/TD]
[TD]Other[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0002[/TD]
[TD]Integration Costs[/TD]
[TD]Other[/TD]
[TD]Other[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Chris McNAmee]

[TABLE="width: 633"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Sheet2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Cost Centre[/TD]
[TD]Pay Component Code Description[/TD]
[TD]Pay Component Code[/TD]
[TD] Total [/TD]
[/TR]
[TR]
[TD]0000[/TD]
[TD]Bonus - Superable - Corporate Funded[/TD]
[TD]402.1[/TD]
[TD] $ 621.00[/TD]
[/TR]
[TR]
[TD]0000[/TD]
[TD]First Aid Allow[/TD]
[TD]411[/TD]
[TD] $ 100.47[/TD]
[/TR]
[TR]
[TD]0000[/TD]
[TD]Govt Paid Parental Bonus[/TD]
[TD]412[/TD]
[TD] $ 184,541.34[/TD]
[/TR]
[TR]
[TD]0000[/TD]
[TD]Uniform Repayment[/TD]
[TD]802.1[/TD]
[TD] $ 16,076.31[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.