SUMIF

tony0682

Board Regular
Joined
May 19, 2005
Messages
164
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,016
Office Version
  1. 365
Yes, use ">"&A1

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

tony0682

Board Regular
Joined
May 19, 2005
Messages
164
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
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,016
Office Version
  1. 365
I3 =SUMIF($A$3:$A$22,TEXT(VLOOKUP(H3,$E$3:$F$13,2,0),"00000")&"*",$B$3:$B$22), copied down.
 

Forum statistics

Threads
1,141,589
Messages
5,707,267
Members
421,499
Latest member
Dpbj

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top