Best way to sum by lookup to 2 sheets

[SpudNix]

Hi
I have 3 sheets and need to sum Sheet1 based on amount from Sheet2 and the sum Criteria on Sheet3
Sheet1 is what I'm trying to sum in "SumAmount"
Sheet1

EmployeeCategory	SumAmount
Payroll	3
Savings	7
Contractors	15

Sheet2: (contains what needs to be summed)

LedgerAccount	Amount
600- Fulltime	1
601 - PartTime	2
603 - Staff Savings	7
604 - Casual Staff	4
605 - Contractors	5
606 - Consultants	6

Sheet 3 (contains the lookups)

EmployeeCategory	LedgerAccount
Payroll	600- Fulltime
Payroll	601 - PartTime
Savings	603 - Staff Savings
Contractors	604 - Casual Staff
Contractors	605 - Contractors
Contractors	606 - Consultants

Thanks in Advance!

 

[SunnyAlv]

idk whats ur excel version but, try this :

On Sheet 1 :

=SUM(XLOOKUP(FILTER(Sheet3!$B$1:$B$7,Sheet3!$A$1:$A$7=$A2),Sheet2!$A$1:$A$7,Sheet2!$B$1:$B$7))

*Sheet1

*Sheet 2

*Sheet 3