英文:
SUMIF Specific Numbers Within a Formula Based on Position of Date as Text in Adjacent Cell
问题
我被指派根据特定日期对数值进行求和。通常，人们可以使用SUMIF公式来实现这一点，例如=SUMIF('Mar 2017'!$K:$K,$A3,'Mar 2017'!$J:$J)。只要'Mar 2017'!$K:$K中的每个单元格中只有一个日期，这个公式就能正常工作。
如果在'Mar 2017'!$K:$K中的某些单元格中有多个日期，任务就变得更加困难，例如...
10/10/2017
3/1/2017
9/25/2018
...这些日期对应于'Mar 2017'!$J:$J中包含多个嵌入数字的公式，例如...
=416.19+1987.92+1064.14
我正在尝试编写一个公式，它将查找$A3中引用的日期，在'Mar 2017'!$K:$K中找到相应的匹配项，然后将公式中相邻单元格中找到的数字添加到计算中，该数字对应于日期在'Mar 2017'!$K:$K中的位置。
例如，假设$A3包含以下日期：3/1/2017。假设'Mar 2017'!K6也包含3/1/2017，'Mar 2017'!J6包含$1,564.20。在这种情况下，我正在寻找的公式将像常规的SUMIF一样工作，例如=SUMIF('Mar 2017'!$K:$K,$A3,'Mar 2017'!$J:$J)，并将$1,564.20包括在计算中。
但是，让我们假设'Mar 2017'!K9包含多个日期，就像我上面提到的例子一样...
10/10/2017
3/1/2017
9/25/2018
...而'Mar 2017'!J9包含以下公式：=416.19+1987.92+1064.14。
我需要的是一个公式，它只计算'Mar 2017'!J9中公式中包含的数字的第二个位置上的数字——1987.92，它对应于3/1/2017在'Mar 2017'!K9中找到的第2个位置。
我已经尝试过以下公式，但没有成功：
=SUM(IFNA(FILTER(VALUE(INDEX(SPLIT('Mar 2017'!$J:$J, "+"), , MATCH(TEXT($A3, "mm/dd/yyyy"), SPLIT('Mar 2017'!$K:$K, CHAR(10)), 0))), SPLIT('Mar 2017'!$K:$K, CHAR(10))=TEXT($A3, "mm/dd/yyyy"))))
和
=SUM(IFNA(FILTER(VALUE(SPLIT('Mar 2017'!$J:$J, "+")), IF(ISNUMBER(SEARCH(TEXT($A3, "mm/dd/yyyy"), 'Mar 2017'!$K:$K)), SPLIT('Mar 2017'!$K:$K, CHAR(10))=TEXT($A3, "mm/dd/yyyy"), FALSE))))
对此问题的任何帮助将不胜感激。
英文:
I am tasked with summing up numerical values based on specific dates. Normally, one might use a SUMIF formula to do this, such as =SUMIF('Mar 2017'!$K:$K,$A3,'Mar 2017'!$J:$J). This works fine as long as there is just one date in each cell in 'Mar 2017'!$K:$K.
The task becomes much harder if there are multiple dates in certain cells within 'Mar 2017'!$K:$K, such as…
10/10/2017
3/1/2017
9/25/2018
…that correspond to cells in 'Mar 2017'!$J:$J which contain formulas with multiple numbers embedded in them, such as…
=416.19+1987.92+1064.14
I am trying to come up with a formula that will look for the date referenced in $A3, find its match in 'Mar 2017'!$K:$K, then add to its calculation the number within the formula found in the adjacent cell in 'Mar 2017'!$J:$J that corresponds to the position the date was found in 'Mar 2017'!$K:$K.
For instance, let's suppose that $A3 contains the following date: 3/1/2017. Let's also say that 'Mar 2017'!K6 also contains 3/1/2017 and 'Mar 2017'!J6 contains $1,564.20. In that case, the formula I'm looking for would work just like a regular SUMIF — such as =SUMIF('Mar 2017'!$K:$K,$A3,'Mar 2017'!$J:$J) — and would include $1,564.20 in its calculation.
But let's then assume that 'Mar 2017'!K9 contains multiple dates, such as the example I mentioned above…
10/10/2017
3/1/2017
9/25/2018
…and 'Mar 2017'!J9 contains the following formula: =416.19+1987.92+1064.14.
What I need is a formula that will calculate only the number in the second position of the numbers contained in the formula in 'Mar 2017'!J9 — 1987.92 — which corresponds to the 2nd position that 3/1/2017 is found in 'Mar 2017'!K9.
I've already tried these formulas without success:
=SUM(IFNA(FILTER(VALUE(INDEX(SPLIT('Mar 2017'!$J:$J, "+"), , MATCH(TEXT($A3, "mm/dd/yyyy"), SPLIT('Mar 2017'!$K:$K, CHAR(10)), 0))), SPLIT('Mar 2017'!$K:$K, CHAR(10))=TEXT($A3, "mm/dd/yyyy"))))
and
=SUM(IFNA(FILTER(VALUE(SPLIT('Mar 2017'!$J:$J, "+")), IF(ISNUMBER(SEARCH(TEXT($A3, "mm/dd/yyyy"), 'Mar 2017'!$K:$K)), SPLIT('Mar 2017'!$K:$K, CHAR(10))=TEXT($A3, "mm/dd/yyyy"), FALSE))))
Any help on this issue will be much appreciated.
答案1
得分: 2
<!-- language-all: js -->
*您可以尝试：*
=let(Σ,map(J:J,K:K,lambda(j,k,iferror(let(x,split(k,char(10)),y,xmatch(A3,x),hstack(choosecols(iferror(split(formulatext(j),"=+"),j),y),choosecols(x,y)))))),
sum(ifna(filter(index(Σ,,1),index(Σ,,2)=A3))))
[![在这里输入图片描述][1]][1]
[1]: https://i.stack.imgur.com/AUDsv.png
英文:
<!-- language-all: js -->
You may try:
=let(Σ,map(J:J,K:K,lambda(j,k,iferror(let(x,split(k,char(10)),y,xmatch(A3,x),hstack(choosecols(iferror(split(formulatext(j),"=+"),j),y),choosecols(x,y)))))),
sum(ifna(filter(index(Σ,,1),index(Σ,,2)=A3))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论