SUM greater than but less than...

[coccio]

Hi,

A2:A10 account #
B2:B10 DATA

Acct DATA
100 7
110 6
115 5
116 4
118 8
120 8
130 5
160 4
165 9


I wan to sum colum B but I want to sum it greater than Account # "118" but less than account # "160"

I use the SUMIF funtion to get ether greater or less than figures but I can't seem to combined them in a formula to get the greater than but less than figure?

coccio

[Aladin Akyurek]

On 2002-05-21 06:13, coccio wrote:
Hi,

A2:A10 account #
B2:B10 DATA

Acct DATA
100 7
110 6
115 5
116 4
118 8
120 8
130 5
160 4
165 9


I wan to sum colum B but I want to sum it greater than Account # "118" but less than account # "160"

I use the SUMIF funtion to get ether greater or less than figures but I can't seem to combined them in a formula to get the greater than but less than figure?

coccio

=SUMIF($A$2:$A$10,">=118",$B$2:$B$10)-SUMIF($A$2:$A$10,">=160",$B$2:$B$10)

This means >=118 and <160. Adjust to suit.

[sen_edp]

use this array formula

remember ctrl-shift-enter when entering it

{=SUM(IF($A$2:$A$10>118,IF($A$2:$A$10<160,$B$2:$B$10,0),0))}

_________________
Best Regards,
Andreas
using Excel 2000 - Windows 2000 prof.

[ This Message was edited by: sen_edp on 2002-05-21 06:32 ]

[rich987652]

As an alternative, you can use the DSUM function. The formula is very simple and doesn't require arrays.
Use the function editor and it explains the function very simply.

Richard