SUMIF - how between two numbers

Greetings,

I want to SUMIF if the value of cells are between 5000 
and 10000?

Note: I am not summing anything.  I have already SUM a 
colum.  But for the next phase of my project, I have used 
SUMIF.  If it is under 5000, it is displayed in a cell.  
I then take that sum and use it elsewhere.  If it is 
between 5000 and 10000, I need to have it displayed in 
that cell and then it will be used in a different 
functuon elsewhere.

I know that if I want to add amounts under 5000 it would 
be:   =SUMIF(I10,"<5000.00",I10) but what is the symbol 
or word and way to write "between" two amounts.

In normal type, it would be "5000-1000" how is it in 
EXCEL language

TIA
-Paul
0
pl1 (1)
9/29/2003 6:35:19 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
818 Views

Similar Articles

[PageSpeed] 25

Paul,

Try
=SUMPRODUCT((A1:A100>=5000)*(A1:A100<=10000),(A1:A1000))

change range to suit, and you may want > 5000 and < 10000.



--

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Paul Lengyel" <pl@LECorp.net> wrote in message
news:1873c01c386b8$6f657390$a601280a@phx.gbl...
> Greetings,
>
> I want to SUMIF if the value of cells are between 5000
> and 10000?
>
> Note: I am not summing anything.  I have already SUM a
> colum.  But for the next phase of my project, I have used
> SUMIF.  If it is under 5000, it is displayed in a cell.
> I then take that sum and use it elsewhere.  If it is
> between 5000 and 10000, I need to have it displayed in
> that cell and then it will be used in a different
> functuon elsewhere.
>
> I know that if I want to add amounts under 5000 it would
> be:   =SUMIF(I10,"<5000.00",I10) but what is the symbol
> or word and way to write "between" two amounts.
>
> In normal type, it would be "5000-1000" how is it in
> EXCEL language
>
> TIA
> -Paul


0
bob.phillips (411)
9/29/2003 6:43:32 PM
Reply:

Similar Artilces:

sequential number
I want to a sequential number to fill in automatically each time the form is filled out. Malissa, A simple way would be to use something like this, you could assign it to a button, an open or before print event. Sheets("Sheet1").Range("A1").Value = _ Sheets("Sheet1").Range("A1").Value + 1 For other ways to do this or if this is going to be used in a temple have a look here http://www.mcgimpsey.com/excel/udfs/sequentialnums.html -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others...

Export to excel from ASP.Net
I have a page wherein the contents of a datagrid are exported to an excel file. All the columns are correctly displayed in excel except for the number columns. The formatting string for the number column is {0:#,##0.00; (#,##0.00);0.00}. The data is formatted for all numbers exceeding 1000 but for any number below 1000 the data is displayed as 1000 and not with 0's in the decimal places. (displayed as 999 instead of 999.00). ...

Question on Returns to Vendor without PO & Receipt number
Our company switched to Great Plains Dynamics from Simply Accounting 3 months ago. We currently need to return some of the inventory items to our vendors. However, when we try to enter a transaction entry in Purchasing/Returns Transaction Entry, in the detail lines we are asked for the PO number or the receipt number. We are not allowed to go further or save without the PO number or receipt number. When we converted from Simply Accounting, all we entered was the beginning balance of the vendors and only the PO's which we expect to receive. The older PO's were not carried over. W...

Emailing Tracking Numbers
I have integrated a website with RMS. When orders come in, I make them a workorder and send a PO to the distributor that will ship the product. At the end of the day, the distrbutor sends me the tracking number. When I bring up the customers order and enter the tracking number, I then complete the transcation, then press Shift/Control/F8 to mark the order processed. Does this send the tracking number to the customer? If not, how can I do that with out going to Outlook and physically doing so? Bill, RMS does not send one. Sounds like a good idea though. Maybe add it to the sugge...

Conditional format that higlights differing data on two worksheets
I have a workbook that contains a worksheet for a single week of any given month and in the sheet I have an individual's time reported for each day of the week. I have a second workbook that contains an individual's time reported by each day for the entire month. I would like to compare the two to determine if there is a mismatch and highlight those cells. The logic goes something like this: (1) I need to match person A in column C of workbook1 to the same name in column C in workbook2. (2) I then need to match the date of the month on workbook 1 & 2 for person A in step #1. (3)...

Viewing / Printing more than two pages at once
I'm working on a publication, and I'd like to get a proper overview of it (balance of pictures etc) However, I can only view two pages at a time. Is it possible to view lots of little pages - say 8-10 at a time? Like you can in Powerpoint. If not, can I print lots of pages on one sheet? In the Print setup I can only get two pages at a time. Only in print preview. On the toolbar there is an icon that you can select to show you multiple pages. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "KMcA" <KMcA@discus...

Select a certain number of cells in a row
Hi, I'd like to have the macro to select row 5 to 10 in the active column. May I know what is the VB code to write? Regards, Valerie maybe... dim myRng as range dim myCol as long with activesheet mycol = activecell.Column set myrng = .range(.cells(5,mycol),.cells(10,mycol)) end with myrng.select ======= or with activesheet .cells(5,activecell.column).resize(6).select end with I'm not quite sure why you want to select that range. But for the most part, if you act directly on the range (and avoid .selects), you're code will work faster and be easier to modify. Dolph...

Window's verson numbers
I'm writing an app in C++ using VS.net and I need to do this based on the OS. I can get the OS version number using _osver global variable found in the include STDLIB.H. But I need a list of what version number correspond to what OS so I know what the value in _osver means. Does anyone know where I can find these definitions? Also the major and minor builds might be useful? If this is the wrong newsgroup could you direct me to the right one? Thank Lots, Neil Neil B wrote: >I'm writing an app in C++ using VS.net and I need to do this based on the OS. >I can get the OS ver...

Mail Delivery Locally Between Two Exchange servers Locally
Hi, 1) I have two Exchange servers hosting Exchange 2000, Exchange 2003 in the same subnet. 2) two domains are different from each other (DmainA.com.sg, DonainB.com.sg). 3) When these exchange servers send mails, it will go through the SMTP of its own. 4) when DomainA send mail to DomainB or viseversa it will go through the SMTP which results slow delivery of mail. Q:- 1) is there any way i can configure these servers to deliver mail to each other directly. 2) on Exchange 2000 any mail for DomainB must go through any conector,deliver directly or smar...

2007 Slide Numbering doesn't work
I have a presentation in which the numbering doesn't work...I've gone step by step through everything I can find but I don't see it. Checked the master to ensure a placeholder was there, that the text is visible. I click on the slide thumbnail, click insert / slide number, check off slide numbering, click apply to all and okay. Nothing appears. When I go back to insert / slide number, nothing is checked. Any thoughts? Try ticking the option for slide number in Insert | Header and Footer instead. -- Echo [MS PPT MVP] http://www.echosvoice.com What's ne...

How To Allocating Rent Between Two Categories?
Original Question: [The part I need help with is that the amount in column G has to be allocated to the following accounts in this order Gas, Hydro, Promo FC CAM Mkt CAM Base % Rent I'm just working on allocating the last two, so I'm assuming that there is rent remaining to be allocated from column G after allocating to Gas, Hydro, Promo, FC CAM and Mkt CAM. The remaining balance has to be allocated first to base rent than to % rent on a monthly basis. If some rent is allocated to % rent, and the next months sales are so low that the base rent is fully filled we need to first reduc...

two smtp address
Hi, I have a client who has an exchange server and uses outlook 2000 for mail messaging. She has her main email address and an additional alias smtp address. She recieves mail from both aliases and when she sends messages it only sends from her main address. Is there a way to change so she can send emails from her other smtp address. Thanks. Randy Well, if she used Ol2002/2003, she could set up a pop or imap (cam use fake pop server name) with the second SMTP address and choose the account to send on... but not with 2000. There is a 3rd party app that she can install that will allo...

macro to generate next number
Hi, i need a macro to look in sheet 2 column A and look at the last filled cell. In the last filled cell i have a number. When i run macro, i need to generate (in sheet1 A1) the number from the last filled cell +1. EX: sheet 2 last filled cell = 29 sheet1 after macro i need to have in A1 = 30 Can this be done? Thanks! Hi, Yuo can have a macro if you want but you don't need one, try this =OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still suffici...

List number of permutations for text nums
I've got 4 digit text nums in A1 down In B1 down, I would like to list the corresponding number of permutations, eg: 7777 1 1777 4 0044 6 2477 12 1234 24 Game for any formula, udf or other vba solution Thanks for insights Sub permutations() Dim I As Integer, J As Integer, Rng As Integer Rng =3D Cells(Rows.Count, "B").End(xlUp).Row J =3D 1 Range("A1").Select Do Until ActiveCell.Value =3D "" For I =3D 1 To Rng Cells(J, "D").Value =3D "'" & ActiveCell & Cells(I, &qu...

page number
How do I remove page number from single pages, not only frontpage You can either insert a section break either side the un-numbered page and change the numbering in the new section or you can conditionally insert the page number in all the pages using fields e.g. {IF {Page} <> 4 "{Page}"} will insert the page number on all pages except page 4. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mv...

Convert Text to Number on 4,508 excel files.
Hi all. I'm faced with a rather unique problem.I have a large amount of improperly formatted excel files. They all have various cells formatted as text, yet they contain numbers and perform calculations on the cells. I now need to get these in a proper state. What I need is: Conversion of all text boxes containing numbers over to proper format of number. Do it automagically. I don't relish the thought of opening each one individually and fixing it. Any thoughts, tips or ideas on this? It could possible if you process that works by Scripting . In Scripting engine 5.6 Filesystem...

Incrementing Numbers ?
Hello, I have a column with, e.g., two numbers in it. Say a 2 and the next one down is a 4. How do I select one or both so that I can simply just drag down and have a long column with: 2 4 6 8 10 etc. And, if there are already a lot of numbers in the column, must one delete them first for the scheme to work ? I tried a few methods, but just can't seem to get it to work. Also, couldn't find anything in the HELP menu for this. Thanks, Bob Select both cells then drag the bottom right corner down. The cursor turns into a small black cross when you hover over the bottom right corner ...

Safe Pay Confirmation Number
Is there anyway to remove the prompt for a confirmation number that Safe Pay displays after you hit the Upload button? I want it to save the last upload date and the results of the ME123504 table without having to enter that number. Thanks, ...

ADO, Recordset and two Access mdbs?
I have two Access database, and I want copy records from DB1 to DB2. If have records from DB1mdb in ADODB.recordset, could I insert records to DB2.mdb (Same table structure/name) directly (whitout creating insert -sql statement)? How? I have two ADODB.connections, ofcourse. I'm using VB6 and ADO... "Major" <lievonen@jyu.fi.HALOOOOOOOO> wrote in message <news:eN9basLuDHA.2304@tk2msftngp13.phx.gbl>... > I have two Access database, > and I want copy records from DB1 to DB2. > > If have records from DB1mdb in ADODB.recordset, > could I insert record...

Difference between two times in seconds
hi i need a favour regarding finding out the time spent between two times in seconds. I need the current time like 12:35:45 PM in a cell A1 and after some time put time in cell A2 as 12:37:50 PM. I need the shortcut formula for putting in a cells (A1&A2) so that it should come in Hr: Min: Sec format. And after that I need the difference of this two times in Seconds (125 seconds above) in cell A3. hi use a custom format in A3. [ss] this will keep the time from rolling over to minutes and total the seconds. regards FSt1 "Radhakant Panigrahi" wrote: ...

Sharing an object between two processes
Hi, I have an application with two modules: 1 .exe and 1 .dll linked together in my project. Into the exe module I created an object Obj of class CObj with some static data member. Then I call a method inside the dll module that uses the object Obj. My problem is that I cannot simply pass the address of Obj to the Dll's method, but I need to share my Obj to access into it directly from the Dll's files. Can you please show me with a simple example how to manage this kind of problem? Maybe should I use the VirtualAlloc function? Mauro hi you can pass the address of the obj to the ...

Finding pairs of numbers
Is this possible? I assume this will involve some VB coding also: Part Num Associated Part Num 123 456 789 5623 8521 6352 456 123 5478 9821 444444 AT256 5623 789 Formula in "C" that would find the mate and insert a "Pairing" number So that the 1st and 4th rows would have a 1 and the 2nd and 7th would have a 2 etc This subroutine will do it - change the range in first statement to suit you needed Sub tryme() Set testrange = Range("A1:C20") For j = 1 To testrange.Count - 1 testA = testrange(j, 1) & testrange(j, 2) Fo...

Top 90% of records
Have the oddest SLA requirement I've ever been asked to have a look at, hope you can help. Basically have a set of data of closed records for 2005. My manager wants to show of the closed records how many days were 90% of them completed? She is looking at this as a 'best case' scenario, so therefore in simplistic terms this means: If there were 50 records closed, look at 90% of these records (in shortest amount of days order) and give the highest number once at 90%. e.g. (with a small dataset). There are 50 records. The largest number of the dataset once sorted is 20 (at the 4...

VBA code to capture Computer device unique ID number
I am seeking knowledge of how to write a VBA code in access 2003 that allows me to call a property function by which I can capture a unique number which identifies the computer hardware device. I am thinking of something like the MAK Number that is associated with the hardware of interest since it is unique and cannot be repeated in any other device. The purpose of this function is that I would grant accessibility for users to my database application only if the hardware can be identified in a table that contains approved MAK numbers. I would greatly appreciate the help I can get in thi...

Converting Hexadecimal number to floating point decimal number #2
Thanks McGimpsey, the hex number will appear in a single sell say A1 a like 0X00 0X0f 0X7b 0Xa1 0x39. What I need is to automatically conver this hex string into a decimal like 2.4e-5. That is automaticall stripping off the prefixes and convert each segment of the hex string Its not just taking a single hex number -- Fif ----------------------------------------------------------------------- Fifi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1232 View this thread: http://www.excelforum.com/showthread.php?threadid=27285 ...