SUMIFS not functioning with , in criteria column

[rodrivers]

I need to SUM data in Column D. A few parameters are required. Column B must be “RB”, Column E must be >=6(inches) and Column F must be greater than 8(august).



The data must fit into this table

SUMIFS function isn’t deciphering between the “,” on Column F and returns 0 as the SUM. Others input the data onto the sheet so there isn’t a possibility to arrange the table differently.
The function doesn't have to be SUMIFS, I just felt this would grab all the required data. I'm inexperienced with UDF and SQL.

Any help would be appreciated.

 

[duggie33]

It might work with SUMIFS, but I couldn't figure it out. You could try it with SUMPRODUCT like below...

Book1
	A	B	C	D	E	F	G
1	Big Creek	RB	Eagle	0	8	5, 6, 7	SCRS
2	Groves Lake	RB	Eagle	2,000	5	5, 6, 7	SCRS
3	Kin ston Creek	RB	Eagle	1,000	8	5, 6, 7	SCRS
4	Smith Creek Reservoir	TT	Tiger	1,000	8	6, 8	MVH
5	Willow Creek Pond	RB	Eagle	500	5	5. 6	GH
6	Willow Creek Pond	RB	Tahoe	1,500	8	8, 9	GH
7
8	RB	1500
9	TT	1000
Sheet1

Cell Formulas
Range	Formula
B8:B9	B8	=SUMPRODUCT(($B$1:$B$6=A8)*($E$1:$E$6>6)*(ISNUMBER(SEARCH("8",$F$1:$F$6,1)))*($D$1:$D$6))

Hope that helps,

Doug

 

[duggie33]

Sorry, I read that as equal to 8 (Aug) instead of greater than. I am spitballing here...maybe you to TEXTSPLIT that column for evaluation.

Doug

 

[duggie33]

With modifications for the greater than month...

Book_2023-10-17.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Species	Strain	Number	Size	Month			Species	Sum of Numbers	Size >=	Month >
2	Big Creek	RB	Eagle	0	8	5, 6, 7	SCRS		RB	3500	6	8
3	Groves Lake	RB	Eagle	2,000	5	5, 6, 7	SCRS		TT	4500	4	7
4	Kin ston Creek	RB	Eagle	1,000	8	5, 6, 7	SCRS
5	Smith Creek Reservoir	TT	Tiger	1,000	8	6	MVH
6	Willow Creek Pond	RB	Eagle	500	5	5, 6	GH
7	Willow Creek Pond	RB	Tahoe	1,500	8	8, 9	GH
8	Big Creek	TT	Eagle	500	7	5, 7, 9	SCRS
9	Groves Lake	TT	Tiger	1,000	8	8, 9, 12	SCRS
10	Kin ston Creek	RB	Eagle	2,000	9	10	SCRS
11	Smith Creek Reservoir	TT	Tahoe	3,000	7	4, 8, 10	MVH
12	Willow Creek Pond	TT	Tiger	1,000	8	5, 6, 7	GH
Sheet1

Cell Formulas
Range	Formula
J2:J3	J2	=SUMPRODUCT(($B$2:$B$12=I2)*($E$2:$E$12>=K2)*(BYROW($F$2:$F$12,LAMBDA(x,MAX(1*TEXTSPLIT(x,", ",,TRUE,0))))>L2)*($D$2:$D$12))

Hope that helps,

Doug