Using cell text in formula - array

[Formula11]

How can I use text in cell for formula with array.

For example in row 2, the formula includes an array.
In row 3 I want to use the contents of cell R3 to indicate the array. But this doesn't work. I tried INDIRECT.

 

[Formula11]

Yes I did think of this but it made no difference.
Also copied and pasted from your add-in output but still get "0".
I also double checked Excel version and it is 365.

Is there something along the lines of DateAmore's solution as this worked for one set of criteria. I was trying to use AND with IF for 2 criteria but this didn't work.

 

[Fluff]

If you type =tex into a cell does the autocomplete offer textbefore & textafter?

 

[Formula11]

No it doesn't.
This is what is given.

 

[Fluff]

Ok, you obviously don't have the new functions quite yet.
How about
=SUM(SUMIFS(D:D,B:B,FILTERXML("<k><m>"&SUBSTITUTE(K3,",","</m><m>")&"</m></k>","//m"),A:A,K2))

 

[Formula11]

Thanks for persisting with this. It now works!

This is the first time I've come across this type of formula, and I presume it will only work with current versions of Excel.

At the same time I looked at DanteAmores solution again and got something to work, see below. This may work on older versions, I don't know. The only issue is that with large sets of data (4000+ rows in my case), my computer struggled and froze on every cell it was calculating, probably because SEARCH is used.

 

[Fluff]

The formula in post#14 should work for 2013 & newer, although it may need the SUM changing to SUMPRODUCT.

 

[Formula11]

OK thanks for confirming.

 

[Fluff]

You're welcome.