SUMIF Specific Numbers Within a Formula Based on Position of Date as Text in Adjacent Cell

Question

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.

Answer 1

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))))