SUMIF

[tony0682]

Is there a way to use SUMIF or an alternate way to SUM a range of cells if something is less than or greater than a criteria?

For example

Book6
	A	B	C	D	E
1	7	91		1	0
2	5	28		2	94
3	9	72		3	58
4	2	17		4	128
5	9	91		5	69
6	6	16		6	16
7	8	100		7	152
8	7	61		8	260
9	8	67		9	163
10	0	25		10	60
11	0	59
12	8	18
13	4	7
14	4	69
15	0	93
16	8	75
17	10	30
18	3	58
19	5	41
20	2	77
21	10	30
22	4	52
Sheet1

This shows the use of sumif using a referenced cell (column e) as a criteria, but is there a way to use< or > in the criteria (e.g.<A1, >A1).. Make note that using 1-10 is only an example.

 

[Oaktree]

Yes, use ">"&A1

e.g. =SUMIF(A1:A100,">"&A1,B1:B100)

 

[tony0682]

Updated SUMIF

Thanks.. Now.. Here is a more specific question with more questions about SUMIF.

Assume these are all on different sheets.

In Sheet 3, column I I'm trying to SUM the numbers in column B if the 5 leftmost digits is equal to Column F. I can't use LEFT of a range. I also want to use a vlookup in the criteria if its possible. The letters in E and H are just theoretical names.. For example, I'm trying to do..
=SUMIF(LEFT($A$3:$A$22,5),VLOOKUP(H3,$E$3:$F$12,2),$B$3:$B$22)

Book8
	A	B	C	D	E	F	G	H	I	J
1	Sheet1				Sheet2			Sheet3
2
3	004375BN0	977,125.00			A	00437		A	0
4	03072SWP6	679,067.00			B	03072		B	0
5	04541GGQ9	973,916.00			C	04541		C	0
6	1266712T2	60,082.00			D	12667		D	0
7	1266715H5	550,368.00			E	22541		F	0
8	126671Y91	508,321.00			F	32027		G	0
9	126673BR2	580,375.00			G	36228		H	0
10	126673JQ6	986,057.00			H	45254		J	0
11	12667FCW3	919,159.00			I	54444		K	0
12	32027NHB0	656,923.00			J	58552
13	36228FD60	715,449.00			K	58992
14	45254NGH4	179,705.00
15	45254NGP6	775,822.00
16	45254NGW1	516,267.00
17	45254NKD8	305,374.00
18	45254NKF3	299,423.00
19	45254TPU2	458,185.00
20	585525FC7	893,532.00
21	5899292M9	521,060.00
22	5899295V6	905,138.00
Sheet1

 

[Oaktree]

I3 =SUMIF($A$3:$A$22,TEXT(VLOOKUP(H3,$E$3:$F$13,2,0),"00000")&"*",$B$3:$B$22), copied down.