calculating sum based on multiple sheets

Question

I am using following formula to calculate sum based on multiple sheets, here is the scenario

Total 2 sheets, Data and Translation,

In Data sheet i have employee name in C Column, Hours in J column,

In Translation sheet i have Department in A Column, Employee name in C column, and Rate in E column, so i have Department name, from that name i need to calculate total $ spent,

I have following formula, that will capture total rate (COUNTIF(H7,Translation!$A$2:$A$27)*Translation!$E$2:$E$27)

but its not calculating total hours (LOOKUP(IF(H7=Translation!$A$2:$A$27,Translation!$C$2:$C$27),'Data '!$C$2:$C$410,'Data '!$J$2:$J$410)

Please let me know

=SUMPRODUCT(IFERROR(LOOKUP(IF(H7=Translation!$A$2:$A$27,Translation!$C$2:$C$27),'Data  '!$C$2:$C$410,'Data '!$J$2:$J$410),0)*COUNTIF(H7,Translation!$A$2:$A$27)*Translation!$E$2:$E$27)

Answer 1

You can't use LOOKUP here because the lookup range isn't sorted ascending - you can use SUMIF in place of LOOKUP, try this "array formula"

=SUM(SUMIF(Data!C2:C500,IF(B2=Translation!A2:A500,Translation!C2:C500),Data!J2:J500)*COUNTIF(B2,Translation!A2:A500)*Translation!E2:E500)

confirmed with CTRL+SHIFT+ENTER