Need to sum everything with a code with a 70

[richmcgill]

I need to be able to sum everything with a code 70-79. I need to exclude anything that has a different number than a 70-79.
All codes begin with a number but the second digit in the report is what I need to target.
The number always has a space between the two digits.
i.e. 1 70
Some of the numbers are negatives but still sum if it has a 70-79 code.
Make sense?

Code		1 71	1 72	1 75	3 11	1 72	1 73	1 72	1 73	3 51	3 11	1 72	1 72	1 73	3 11	1 73
Amount	Sum if the Code (row 1) is a 70 ( 1 72) number	$1,553.06	$1,553.06	$1,553.06	'-$2,460.29	$1,553.06	$449.08	$1,517.23	$1,517.23	'-$1,504.00	'-$2,120.00	$1,517.23	$1,517.23	$1,517.23	'-$1,971.21	$1,517.23

 

[JamesCanale]

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
13	Code		1 71	1 72	1 75	3 11	1 72	1 73	1 72	1 73	3 51	3 11	1 72	1 72	1 73	3 11	1 73
14	Amount	$15,764.70	$1,553.06	$1,553.06	$1,553.06	($2,460.29)	$1,553.06	$449.08	$1,517.23	$1,517.23	($1,504.00)	($2,120.00)	$1,517.23	$1,517.23	$1,517.23	($1,971.21)	$1,517.23
Sheet25

Cell Formulas
Range	Formula
B14	B14	=SUM((VALUE(RIGHT(C13:Q13,3))>=70)*(VALUE(RIGHT(C13:Q13,3))<80)*($C$14:$Q$14))

 

[FormR]

Hi, here's another option that might work depending on how consistent your "codes" are.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Code		1 71	1 72	1 75	3 11	1 72	1 73	1 72	1 73	3 51	3 11	1 72	1 72	1 73	3 11	1 73
2	Amount	15764.7	1553.06	1553.06	1553.06	-2460.29	1553.06	449.08	1517.23	1517.23	-1504	-2120	1517.23	1517.23	1517.23	-1971.21	1517.23
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUMIFS(C2:Q2,C1:Q1,"? 7*")

 

[richmcgill]

Hi, here's another option that might work depending on how consistent your "codes" are.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Code		1 71	1 72	1 75	3 11	1 72	1 73	1 72	1 73	3 51	3 11	1 72	1 72	1 73	3 11	1 73
2	Amount	15764.7	1553.06	1553.06	1553.06	-2460.29	1553.06	449.08	1517.23	1517.23	-1504	-2120	1517.23	1517.23	1517.23	-1971.21	1517.23
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUMIFS(C2:Q2,C1:Q1,"? 7*")

Thank you! This one worked very well.

 

[richmcgill]

Thank you! This one worked very well.

What would it look like if I wanted to count all of the ones begin
with a 3 51 like in the example.
But matching the whole 3 51 number.

 

[Dave Patton]

post 5 Your question is not clear. What did you try?

Consider the following:

T202501a.xlsm
	A	B
17		-1,504.00
18		-8,055.50
19
3a

Cell Formulas
Range	Formula
B17	B17	=SUMIFS(C14:Q14,C13:Q13,"3 51")
B18	B18	=SUMIFS(C14:Q14,C13:Q13,"3*")

 

[richmcgill]

I was able to figure it out. I appreciate your help!