英文:
Sum If but I only want to sum if the referenced equipment is in the list, return an error not a zero if it isn't in the list
问题
我想要对设备的计数进行求和，如果设备在列表中的话。我有两个列表，一个是部分集合，一个是完整集合。我想要从部分集合中对计数求和，然后放到主集合旁边，但是当设备不在第二个集合中时不要求和为零。
示例：
主列表
| A | B |
|---|---|
| 设备 | 求和计数 |
| 设备 1 | 5 |
| 设备 2 | 返回错误，不是零 |
| 设备 3 | 3 |
第二个列表
| C | D |
|---|---|
| 设备 | 计数 |
| 设备 1 | 3 |
| 设备 1 | 2 |
| 设备 3 | 1 |
| 设备 3 | 2 |
Sumif 不适用，因为它在设备不列出时返回零求和。
英文:
I want to sum the counts for an equipment if the equipment is in the list. I have two lists, one is a partial set, one is the full set. I want to sum the counts from the partial set, next to the main set, but not sum to zero when the equipment is not in the second set.
Example:
Main List
| A | B |
|---|---|
| Equipment | Sum Counts |
| Equip 1 | 5 |
| Equip 2 | Return error, not zero |
| Equip 3 | 3 |
Second List
| C | D |
|---|---|
| Equipment | Counts |
| Equip 1 | 3 |
| Equip 1 | 2 |
| Equip 3 | 1 |
| Equip 3 | 2 |
Sumif doesn't work because it returns a 0 sum when the equipment isn't listed.
答案1
得分: 1
你可以使用 iferror、sumif 和 na 的组合：
=IFERROR(1/(1/SUMIF(C2:C5,A2,D2:D5)), NA())
这样，如果 sumif 的返回值为0，它将返回一个错误，因为它会强制产生 #DIV/0! 错误。
英文:
You can use a combination of iferror, sumif and na:
=IFERROR(1/(1/SUMIF(C2:C5,A2,D2:D5)), NA())
So this will return an error if the return value of sumif is 0 since it forces a #DIV/0! error.
答案2
得分: 1
简单 (复制到下方):
=SUM(FILTER(D$2:D$5,C$2:C$5=A2))
溢出 (单个单元格):
=LET(slData,C2:C5,srData,D2:D5,dlData,A2:A4,
BYROW(dlData,LAMBDA(dr,SUM(FILTER(srData,slData=dr))))
英文:
Sum Up Filtered Data (Show Errors)
Simple (Copy Down)
=SUM(FILTER(D$2:D$5,C$2:C$5=A2))
Spilled (Single Cell)
=LET(slData,C2:C5,srData,D2:D5,dlData,A2:A4,
BYROW(dlData,LAMBDA(dr,SUM(FILTER(srData,slData=dr)))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论