Sumifs - criteria with curly brackets with reference in 1 cell

[deceiverleo]

Hi Excel masters :D

I would like to find a way to put all my criteria in 1 cell and make reference to it

This will work:

=SUM(SUMIFS(sumrange,criteria_range,{"6*","7*","8*","9*"}))

--------------------------------------------------------------------------

But this will not work:

If I put {"6*","7*","8*","9*"} in cell A1

=SUM(SUMIFS(sumrange,criteria_range,A1))

---------------------------------------------------------------------------

I know if I make {"6*","7*","8*","9*"} into 4 cells 6* , 7*, 8*, 9*, it could work

But this is not what I would like to achieve

May I ask if there is a way to achieve above - to put everything in 1 cell and make reference to it?

 

[bebo021999]

As my knowdledge, its impossible to convert textstring in {} format from a cell to become SUMIFS parameter.

 

[Peter_SSs]

May I ask if there is a way to achieve above - to put everything in 1 cell and make reference to it?

You can if you have the TEXTSPLIT function in your version of 365. You may not have that function yet as shown by the function's Help page
In due course though all 365 subscribers should get it so keep in mind if you don't already have it.

22 08 25.xlsm
	A	B	C	D	E	F
1	6*,7*,8*,9*		1			2
2			1	6a		2
3			1	9b
4			1
5
SUMIFS

Cell Formulas
Range	Formula
F1	F1	=SUM(SUMIFS(sumrange,criteria_range,{"6*","7*","8*","9*"}))
F2	F2	=SUM(SUMIFS(sumrange,criteria_range,TEXTSPLIT(A1,",")))

Named Ranges
Name	Refers To	Cells
criteria_range	=SUMIFS!$D$1:$D$4	F1:F2
sumrange	=SUMIFS!$C$1:$C$4	F1:F2

 

[Dave Patton]

If you do not have TextSplit or a current version of Excel, the following will work.

Name the criteria range with Formulas NameManager.
N.B. The Sum formula does not require Array Enter with current versions of Excel.

T202208a.xlsm
	A	B	C	D	E
1
2
3		2	400	10	10
4		3	700
5		4	400
6		5	400
7		6	400
8		7	600
7c

Cell Formulas
Range	Formula
D3	D3	=SUMPRODUCT(SUMIFS(B3:B8,C3:C8,CritA))
E3	E3	=SUM(SUMIFS(B3:B8,C3:C8,CritA))
Press CTRL+SHIFT+ENTER to enter array formulas.