Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Cam Cam is offline
external usenet poster
 
Posts: 165
Default how to set criteria between 2 numerical values?

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default how to set criteria between 2 numerical values?

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,718
Default how to set criteria between 2 numerical values?

=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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,722
Default how to set criteria between 2 numerical values?

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Only chart numerical values, not error values andy Charts and Charting in Excel 2 November 8th 07 11:46 PM
Adding numerical values based on multiple values in another column Kazmaniac Excel Worksheet Functions 6 April 4th 07 09:53 PM
Numerical values only saziz Excel Discussion (Misc queries) 4 March 17th 06 01:50 AM
Associated Numerical Values kdoggity Excel Discussion (Misc queries) 3 February 24th 06 08:07 PM
need help cancatenating numerical values kier Excel Discussion (Misc queries) 2 May 30th 05 12:04 AM


All times are GMT +1. The time now is 07:27 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"