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.

 

[HongRu]

[Delete]

 

[DanteAmor]

From what I've seen, I don't think it's possible to use indirect function to put multiple criteria in the sumif function.

In fact, to get the first result and add the 3 values, the formula should be like this:

=SUM(SUMIF(B2:B8,{1;3;115},D2:D8))

I show you an option to put the criteria in another cell:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K
1		Part	C	D	E	F	G	H
2		1		8						18
3		3		4						18	1,3,115
4		5		2
5		11		1
6		43		9
7		47		9
8		115		6
D10

Cell Formulas
Range	Formula
J2	J2	=SUM(SUMIF(B2:B8,{1;3;115},D2:D8))
J3	J3	=SUM(IF(ISNUMBER(SEARCH(","&B2:B8&",",","&K3&",")),D2:D8))
Press CTRL+SHIFT+ENTER to enter array formulas.

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
---

 

[HongRu]

try
J3
=SUMIF(B:B,TEXTSPLIT(MID(R3,2,LEN(R3)-2),","),D:D)

 

[Formula11]

Thanks to both for responses.
I was able to get DanteAmore's solution to work.

Is it possible to apply to 2 criteria as well.
This time, include the same array as above and another criteria for Case 1 in column A (this is a single value - not an array).
Sorry I can't load the addin as there are restrictions on my computer.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Formula11]

Ok thanks, this is done.

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		Part	C	D	E	F	G	H
2	1	1		8							1
3	2	3		4						14	1,3,115
4	1	5		2
5	1	11		1
6	2	1		9
7	1	47		9
8	1	115		6
Temp

Cell Formulas
Range	Formula
J3	J3	=SUM(SUMIFS(D:D,B:B,TEXTSPLIT(K3,","),A:A,K2))

 

[Formula11]

Thanks for looking at this.
I can see your method works above but for some reason I get a "0" result. Attempted twice including using the same figures and cells you had.

 

[Fluff]

I suspect your "numbers" are in-fact text & nor real numbers. If you convert them to numbers it should work.