Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
=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. |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Only chart numerical values, not error values | Charts and Charting in Excel | |||
Adding numerical values based on multiple values in another column | Excel Worksheet Functions | |||
Numerical values only | Excel Discussion (Misc queries) | |||
Associated Numerical Values | Excel Discussion (Misc queries) | |||
need help cancatenating numerical values | Excel Discussion (Misc queries) |