基于多张图纸计算总和 [英] calculating sum based on multiple sheets
问题描述
我正在使用以下公式来计算基于多张表的总和,下面是场景
总共2张数据和翻译
在数据表中,我有C列中的员工姓名,J列中的小时数,
在翻译表中,我有一个A栏中的部门,员工C列中的名称和E列中的Rate,所以我有部门名称,从该名称我需要计算总$花费,
我有以下公式,那将捕获总费率(COUNTIF(H7,翻译!$ A $ 2:$ A $ 27)*翻译!$ E $ 2:$ E $ 27)
,但不计算总时数(LOOKUP(IF(H7 = Translation!$ A $ 2:$ A $ 27,Translation!$ C $ 2:$ C $ 27) $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ >
= SUMPRODUCT(IFERROR(LOOKUP(IF(H7 = Translation!$ A $ 2:$ A $ 27,Translation!$ C $ 2:$ C $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ E $ 2:$ E $ 27)
你不能使用 LOOKUP
,因为查找范围没有按升序排序 - 您可以使用 SUMIF
代替LOOKUP ,尝试这个数组公式
= SUM(SUMIF(Data!C2:C500,IF(B2 = Translation!A2:A500,Translation !C2:C500),数据!J2:J500)* COUNTIF(B2,翻译!A2:A500)*翻译!E2:E500)
用确认CTRL + SHIFT + ENTER
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)
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
这篇关于基于多张图纸计算总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!