SUMIFS with multiple criteria and commas

[rodrivers]

I'm trying to SUM data in a spreadsheet that have multiple column criteria and one containing commas.

I need the data in column c with criteria to grab "RB" from column A, ">=6" in column d, and months 7 and less in column E. The comma is creating a problem when using sumif. Manually calculating returns 46,599, but this is used to forecast the upcoming years and the dates, species, size and number change yearly so I can't make the formula dependent on those cells.

Any help would be appreciated. Thank you

 

[DRSteele]

Welcome to the Forum.

I mocked up some sample data to come up with this solution. Note that there can't be anything in your Date column other than numbers and a comma with no spaces.

MrExcel posts20.xlsx
	C	D	E	F	G
5	RB	4	1,2		0
6	SC	9	11,12		0
7	RB	9	3,4,6		3
8	RB	9	4,5		2
9	RB	9	7,8		1
10	SC	3	8		0
11	RB	2	9		0
12	RB	5	6,7		0
Sheet25

Cell Formulas
Range	Formula
G5:G12	G5	=SUM((--(NUMBERVALUE(TEXTSPLIT(E5,","))<8))*(C5="rb")*(D5>5))

 

[rodrivers]

Welcome to the Forum.

I mocked up some sample data to come up with this solution. Note that there can't be anything in your Date column other than numbers and a comma with no spaces.

MrExcel posts20.xlsx
	C	D	E	F	G
5	RB	4	1,2		0
6	SC	9	11,12		0
7	RB	9	3,4,6		3
8	RB	9	4,5		2
9	RB	9	7,8		1
10	SC	3	8		0
11	RB	2	9		0
12	RB	5	6,7		0
Sheet25

Cell Formulas
Range	Formula
G5:G12	G5	=SUM((--(NUMBERVALUE(TEXTSPLIT(E5,","))<8))*(C5="rb")*(D5>5))

I would need the total "number"(column C) summed fitting those parameters