# SUMIF

#### tony0682

##### Board Regular
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
ABCDE
179110
2528294
3972358
42174128
5991569
6616616
781007152
87618260
98679163
100251060
11059
12818
1347
14469
15093
16875
171030
18358
19541
20277
211030
22452
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.

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Yes, use ">"&A1

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

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
ABCDEFGHIJ
1Sheet1Sheet2Sheet3
2
3004375BN0977,125.00A00437A0
403072SWP6679,067.00B03072B0
504541GGQ9973,916.00C04541C0
61266712T260,082.00D12667D0
71266715H5550,368.00E22541F0
8126671Y91508,321.00F32027G0
9126673BR2580,375.00G36228H0
10126673JQ6986,057.00H45254J0
1112667FCW3919,159.00I54444K0
1232027NHB0656,923.00J58552
1336228FD60715,449.00K58992
1445254NGH4179,705.00
1545254NGP6775,822.00
1645254NGW1516,267.00
1745254NKD8305,374.00
1845254NKF3299,423.00
1945254TPU2458,185.00
20585525FC7893,532.00
215899292M9521,060.00
225899295V6905,138.00
Sheet1

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

Replies
2
Views
155
Replies
9
Views
437
Replies
6
Views
306
Replies
4
Views
302
Replies
7
Views
152

1,219,672
Messages
6,149,616
Members
450,904
Latest member
Gracifer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back