Only sum if value is within a range

[rfinnegan]

Hello All:

I'm trying to sum the values in cells C7:AG69 if they are between -$499 and +$499. I've tried several SUMIF formulas and I can't seem to get it to work.

I think this should be simple, but I just can't figure it out....which I guess is a way of saying I'm an idiot.

Thanks in advance.

 

[pedie]

try something like this

=SUMIFS(C7:AG69,C7:AG69,">=-499",C7:AG69,"<499")

 

[Nogslaw]

If you are using XL2007 or later, check out the SUMIFS function. It allows you to enter multiple criteria for your range.

If not, I'm sure there's a better way, but this is the first one I thought of to try:

=SUMIF(C7:AG69,">= -499")- SUMIF(C7:AG69, ">499")

 

[Aladin Akyurek]

Hello All:

I'm trying to sum the values in cells C7:AG69 if they are between -$499 and +$499. I've tried several SUMIF formulas and I can't seem to get it to work.

I think this should be simple, but I just can't figure it out....which I guess is a way of saying I'm an idiot.

Thanks in advance.

Control+shift+enter, not just enter:

=SUM(IF(ABS(C7:AG69)<=499,C7:AG69))

 

[T. Valko]

Hello All:

I'm trying to sum the values in cells C7:AG69 if they are between -$499 and +$499. I've tried several SUMIF formulas and I can't seem to get it to work.

I think this should be simple, but I just can't figure it out....which I guess is a way of saying I'm an idiot.

Thanks in advance.

Try this array formula**:

=SUM(IF(C7:AG69>=-499,IF(C7:AG69<=499,C7:AG69)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[rfinnegan]

Thanks to all, I'm getting the results I expected now.

I should have mentioned I'm using Excel 2003...

 

[T. Valko]

Thanks to all, I'm getting the results I expected now.

I should have mentioned I'm using Excel 2003...

You're welcome. Thanks for the feedback!

I used to work with a R. Finnegan!

 

[Aladin Akyurek]

Thanks to all, I'm getting the results I expected now.

I should have mentioned I'm using Excel 2003...

You are welcome. Thanks for providing feedback. By the way, the formula with SUMIF would be pretty adequate for the task.