尝试根据在不同工作表之间比较的条件来对一列中的元素求和。

huangapple go评论57阅读模式
英文:

Trying to sum elements in a column based on comparing criteria between sheets

问题

I have two sheets: Backlog and Sprint. The backlog items are numbered 1, 2, 3, 4 and so on, and the sprint items are numbered 1.1, 1.2, 1.3, 1.4 and so on.

In the backlog I need to sum the hours spent from column J3:J in the sprint sheet for the items with the same initial ID (1, 2, 3, 4...).

I'm trying to do this but I get 0, even though the sum of column J with values 1.X in col A is 4.5.

=SUMIF(A3,"="&INDEX(SPLIT('Sprint 1'!$A$3:$A,"."),1),'Sprint 1'!$J$3:$J)

The IDs are strings, if that matters. They need to be so because...reasons 尝试根据在不同工作表之间比较的条件来对一列中的元素求和。

英文:

I have two sheets: Backlog and Sprint. The backlog items are numbered 1, 2, 3, 4 and so on, and the sprint items are numbered 1.1, 1.2, 1.3, 1.4 and so on.

In the backlog I need to sum the hours spent from column J3:J in the sprint sheet for the items with the same initial ID (1, 2, 3, 4...).

I'm trying to do this but I get 0, even though the sum of column J with values 1.X in col A is 4,5.

=SUMIF(A3;"="&INDEX(SPLIT('Sprint 1'!$A$3:$A;".");1); 'Sprint 1'!$J$3:$J)

The IDs are strings, if that matters. They need to be so because...reasons 尝试根据在不同工作表之间比较的条件来对一列中的元素求和。

答案1

得分: 1

以下是翻译好的内容:

=SUMIF(MAP(A:A;LAMBDA(Z;INDEX(SPLIT(Z;"."));1)));D1;B:B)

英文:

Here's a generalized example of how it can be done:

=sumif(map(A:A;lambda(z;index(split(z;".");1)));D1;B:B)

尝试根据在不同工作表之间比较的条件来对一列中的元素求和。

huangapple
  • 本文由 发表于 2023年3月3日 21:11:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/75627511.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定