Hello,

I am trying to look for a correct formula to return the sum of the range
between the operation range. I tried this, but didn't work as it returns zero.
Sample data:
A B C D E
10 2
20 1.5
30 12.0
40 4

=SUMIF($A$6:$A$64,"=20 And <=30",$B$6:$B$64)

Result want: 1.5 + 12.0 = 13.5

Any sugguestion is appreciated.

One way:

=SUMIF(A6:A64,"=20",B6:B64)-SUMIF(A6:A64,"30",B6:B64)

--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Hello,

I am trying to look for a correct formula to return the sum of the range
between the operation range. I tried this, but didn't work as it returns
zero.
Sample data:
A B C D E
10 2
20 1.5
30 12.0
40 4

=SUMIF($A$6:$A$64,"=20 And <=30",$B$6:$B$64)

Result want: 1.5 + 12.0 = 13.5

Any sugguestion is appreciated.

=SUM(SUMIF(A6:A64,{"=20","30"},B6:B64)*{1,-1})


"Cam" wrote:

Hello,

I am trying to look for a correct formula to return the sum of the range
between the operation range. I tried this, but didn't work as it returns zero.
Sample data:
A B C D E
10 2
20 1.5
30 12.0
40 4

=SUMIF($A$6:$A$64,"=20 And <=30",$B$6:$B$64)

Result want: 1.5 + 12.0 = 13.5

Any sugguestion is appreciated.

SUMIF won't let you do more than 1 criteria (I hear you can in 2007), but
this will work for you:

=SUMPRODUCT(($A$6:$A$64=20)*($A$6:$A$64<=30)*($B$ 6:$B$64))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Cam" wrote:

Hello,

I am trying to look for a correct formula to return the sum of the range
between the operation range. I tried this, but didn't work as it returns zero.
Sample data:
A B C D E
10 2
20 1.5
30 12.0
40 4

=SUMIF($A$6:$A$64,"=20 And <=30",$B$6:$B$64)

Result want: 1.5 + 12.0 = 13.5

Any sugguestion is appreciated.