SUMIFS and GREATER THAN OR EQUAL TO

[AK_Excel_13]

Column A contains my footage and Column B contains rating value.
I want to write a sumifs function that will tell me the amount of feet that have a rating value greater than 500 but less than or equal to 600.
How do I do write the formula?

Here is what I have so far, but it doesn't work.
=sumifs(A2:A5000,B2:B5000,>500 or <=600)

Thanks!

 

[Weazel]

maybe something like...

=SUMIFS(A2:A6,B2:B6,">500",B2:B6,"<=600")

 

[AK_Excel_13]

That worked!
Thanks!

 

[AK_Excel_13]

I just realized that should be an "and" and not an "or"

 

[Weazel]

glad you got it working

 

[fazeez01]

=SUMIFS(A2:A6,B2:B6,">500",B2:B6,"<=600")

instead of the 500, can a cell reference be inserted into the formula? I want to reference a cell where I'll be changing the value.

Thanks

 

[jtakw]

Hi,

Yes, sample below, change the >= and/or <= as you require:

Book1
	A	B	C	D	E
1	100	550	Min	Max	Result
2	200	300	500	600	2000
3	300	200
4	400	800
5	500	750
6	600	10
7	700	300
8	800	900
9	900	500
10	1000	520
Sheet29

Cell Formulas
Range	Formula
E2	=SUMIFS(A1:A10,B1:B10,">="&C2,B1:B10,"<="&D2)

 

[Aladin Akyurek]

=SUMIFS(A2:A6,B2:B6,">500",B2:B6,"<=600")

instead of the 500, can a cell reference be inserted into the formula? I want to reference a cell where I'll be changing the value.

Thanks

If E1 houses >500 and F1 <=600, then:

=SUMIFS($A$2:$A$6,$B$2:$B$6,E1,$B$2:$B$6,F1)

If E1 houses jus 500 and F1 600, then:

=SUMIFS($A$2:$A$6,$B$2:$B$6,">"&E1,$B$2:$B$6,"<="&F1)

​