multiple range in SUM,IF

=SUM(IF(('13b'!A3:A75="Customer 
1")*('13b'!C3:C75="QAR")*('13b'!H3:H75>31<=93),('13b'!F3:F75),""))

Im above array formula, 3rd criteria i am trying to set range > 31 AND <=93
but it doesnt work .. could anyone help ??
0
Utf
12/16/2009 6:06:02 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
737 Views

Similar Articles

[PageSpeed] 33

=SUM(IF(('13b'!A3:A75="Customer 
1")*('13b'!C3:C75="QAR")*('13b'!H3:H75>31)*('13b'!H3:H75<=93),('13b'!F3:F75),"")) 
as an array formula
or (not an array formula) either
=SUMPRODUCT(('13b'!A3:A75="Customer 
1")*('13b'!C3:C75="QAR")*('13b'!H3:H75>31)*('13b'!H3:H75<=93)*('13b'!F3:F75))
or
=SUMPRODUCT(--('13b'!A3:A75="Customer 
1"),--('13b'!C3:C75="QAR"),--('13b'!H3:H75>31),--('13b'!H3:H75<=93),('13b'!F3:F75))
--
David Biddulph

"Sam" <Sam@discussions.microsoft.com> wrote in message 
news:ED1FB426-6D9D-41DF-BE3B-E904FAEF7B8D@microsoft.com...
> =SUM(IF(('13b'!A3:A75="Customer
> 1")*('13b'!C3:C75="QAR")*('13b'!H3:H75>31<=93),('13b'!F3:F75),""))
>
> Im above array formula, 3rd criteria i am trying to set range > 31 AND 
> <=93
> but it doesnt work .. could anyone help ?? 


0
David
12/16/2009 6:34:57 AM
=SUMPRODUCT(('13b'!A3:A75="Customer1")*('13b'!C3:C75="QAR")*('13b'!H3:H75>31)*('13b'!H3:H75<=93),('13b'!F3:F75))

Check whether the A3:A75 criteria is "Customer1" or "Customer 1".  If it is 
Customer 1 then change the A3:A75 criteria in the formula also.  Since there 
was an unfortunate Paragraph Mark in your post, so I could not able to 
identify.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Sam" wrote:

> =SUM(IF(('13b'!A3:A75="Customer 
> 1")*('13b'!C3:C75="QAR")*('13b'!H3:H75>31<=93),('13b'!F3:F75),""))
> 
> Im above array formula, 3rd criteria i am trying to set range > 31 AND <=93
> but it doesnt work .. could anyone help ??
0
Utf
12/16/2009 6:54:01 AM
Reply:

Similar Artilces:

Naming Ranges #4
Hi, When I name a Range on Sheet1, then make a Copy of Sheet1 to say Sheet2, then access the Define Names dialogbox, I can see the named Range listed twice. One is noted as belonging specifically to Sheet2 and the other appears without a sheet reference. When assiging a name to a Range, can I specify that it belongs to a certain sheet? Also, how would I change this code to assign the sheet name aswell? ActiveWorkbook.Names.Add Name:="myRange", RefersToR1C1:="=Sheet1!R1C1:R10C1" I am trying to assign a the name 'Column_Header_Range' to each sheet in my work...

Excel: Enable UnHiding of multiple sheets at once
When you have multiple sheets hidden in workbook and want to unhide more tha one, you must go back thru the dialog box to unhide each. Why not write this one to be like most others so you can shift-click/ctrl-click the sheetnames you want to unhide while you're ther ONCE and unhide them all at one time? Cyber http://register.microsoft.com/mswish/suggestion.asp While waiting for MS to make the changes in the next version you could use a macro. Sub Show_Sheets() Dim i As Integer For i = 1 To ActiveWorkbook.Worksheets.Count ActiveWorkbook.Worksheets(i).Visible = True N...

Unreconciling Multiple Rows In Money
I would like to know if there is a way to unreconcile multiple rows in Microsoft Money? I can do one at a time, but I need to do a large number of them and don't want to do them one at a time. I've tried to contact Microsoft directly about this but got passed to the company that I bought my machine from, who passed me off to another support number in their company - which tried to charge me for asking this question. Any info on this would be great! Doug In microsoft.public.money, Doug wrote: >I would like to know if there is a way to unreconcile multiple rows in >Microsoft ...

Sum a column that meets two criteria
I need to sum a column of numbers if it matches two different criteria. I can set up the SUMIF easily for meeting one criteria, but I need to also sum the column if it meets that criteria, and another. For example: A B C 1 150 ABC MS1 2 200 DEF MS0 3 100 LMN MS0 4 125 ABC MS1 5 175 LMN MS1 6 225 DEF MS0 I need to have a formula that would say <<Sum column A IF column B = "DEF" AND column C = "MS0">>. (and so forth for the other combinations). I know there has to be a way to do this, probably using a combination of an IF and SUMIF functions - but i keep...

Compare records in multiple sheet -> report
Hi! I have a workbook consisting of ten sheets. To simplify my question let�s say that the three first columns of every sheet denotes the spatial coordinates x,y,z and the fourth column is a scalar value. Some x,y,z-triplets exist in all 10 sheets, some exists in only a few sheets, if the triplet exists, then also the scalar value of the 4th column exists. What I would like to do is to find all unique x,y,z-triplets and show them in the first column of a new sheet. In columns 2-11, I would like to show the scalar value(from the corresponding x,y,z-triplet of course) in column 4 in s...

Printing Issues when distributing a form to multiple users
I am distributing a spreadsheet to multiple users. The spreadsheet is being used as a form for data entry. The problem i am having is that when it is being printed, the users are getting varying page layouts where the page breaks have changed, due the changes to the length of the document growing from the free text they are entering. The page breaks that excel chooses don't seem to be spaced well, leading to one or two of the pages being very short (i.e., leaving a lot of free white space), whereas the other pages are taking up the entire page. Is there a particular page layout selection...

Pivot Table
I've this problem, when I drag the fields into the "Data" area, it will show as "Count of Q1 Results". But what I actually want is "Sum of Q1 Results". I would have to manually go to field setting and reconfigure from count to sum. It happens for all the fields I drag into the "Data" area. Is there any way around this? Thanks. If there are blank cells, or cells with text, in the column, Excel will default to the Count function when the field is added to the data area. If the column contains only numbers, it should default to Sum. Derrick wr...

AUTO SUM
I USED AUTO SUM TO TOTAL SUBSECTIONS OF A COLUMN. THE FIRST 2 SHOWED THE TOTAL CORRECTLY: THE LAST 2 SHOW "#####" IN THE CELL. IF YOU PRINT THE WORKSHEET, THE CORRECT TOTAL PRINTS IN THOSE "#####" CELLS. WHAT AM I DOING WRONG? BJ wrote: > I USED AUTO SUM TO TOTAL SUBSECTIONS OF A COLUMN. THE FIRST 2 SHOWED THE > TOTAL CORRECTLY: THE LAST 2 SHOW "#####" IN THE CELL. IF YOU PRINT THE > WORKSHEET, THE CORRECT TOTAL PRINTS IN THOSE "#####" CELLS. WHAT AM I DOING > WRONG? You just need to resize that column. Type resize column into...

named ranges in other workbooks
i have a range of cells whose values are validated from a list determined by a name the name is determined by a formula which references a range of cells in a different workbook (i'm on a office network...) the problem is: if the other workbook is open (on my desktop?) the name is o.k. and the validation is fine... but: if the other workbook is not open, the name results in an error i didn't think that this is the way it's suppose to work... AURGHH Mark, Do you use a full reference such as ='C:\Documents and Settings\Owner\My Documents\Book1.xls'!profit Bernar...

Print multiple copies per sheet
I have a pubisher file that is a card with a size of 5 x 6 and is 2 pages. Is it possible to print 2 copies of this file on a letter size page so that there will be 2 double sided copies on the letter size page. I need to laminate these cards so if I could get 2 per page I would just need to cut the page in half. I am using Publisher 2003. Thanks in advance for any help. Best regards, Dee Page setup, Business card, change copies per sheet, Side margin 1.25, top margin ..5, zero gaps, okay out, in the next screen change the measurements to width 6" height 5". The above will...

How can I change primary display for presentation on multiple mon.
I want to hit F5 and have my presentation go to a different monitor. I have multiple monitors so my laptop display on a classroom screen. Can I change the default monitor that F5 points to? In article <5A95BBFC-B291-4A05-9E71-3F094B937ABB@microsoft.com>, Shorebilly wrote: > I want to hit F5 and have my presentation go to a different monitor. I have > multiple monitors so my laptop display on a classroom screen. Can I change > the default monitor that F5 points to? In the Slide Show | Set up show dialog, you can choose the monitor you want to display slide s...

Date range for a report
Hi I am trying to run a query and can't seem to nail down the right code. We use the access database to track files, incoming/outgoing correspondence dates etc. I am trying to run a query that show me files with dates in a follow up field of -60 days to +7 days, so essentially any follow ups missed in the last 2 months and up coming in the next week. This report is run on a weekly basis. Appreciate any help Thanks Assuming that the follow up field is actually a date/time data type, try this in the criteria: Between Date() - 60 and Date() + 7 -- Jerry Whittle, ...

Which is faster sum(if) as an array or sumproduct?
I finally got my sum(if) based on more than one condition to work but it takes my 1.8 Ghz chip 7 or 10 minutes to calculate the workbook with the array formulas in 2003 Excel. Is Sumproduct any faster or do I just need to use the free time to do something else? Thanks, Lee It depends. At this link: http://msdn2.microsoft.com/en-us/library/aa730921.aspx there are some VBA routines that will let you time the calculations. You'll find the code about 1/4 of the way down. Biff "Neophyte" <wleecoleman@.nospam.ev1.net> wrote in message news:OqrP4rOcHHA.4260@TK2MSFTNG...

Multiple Resource Entries Against Tasks
I have a project created in Project 2007 which is saved in Project 2000-2003 format as my client only has 2003. Until today this had been fine. However, a really strange problem has ocurred whereby I entered a single resource against a task (on a number of individual tasks) but, on saving, Project allocated the same resource to the task 5 or 6 times. I have attempted to delete these duplicate allocations (by going into task information>resources and selecting & deleting the repeated resource then exiting task information via the save option). Unfortunately, this appear...

Excel 2003
There doesn't seem to be an Office 2003 group, I hope you can help, this is driving me mad! I want to open two different spreadsheets in two different instances of Excel, so I can compare them side by side without switching. Excel XP was OK, but Excel 2003 won't let me, so at the moment I've got one spreadsheet running on a different PC! I've seen a similar question asked many times, but the answer to use: Tools | Options | View Tab | Show| Windows in Taskbar does *not* fix the problem, this just lets you switch windows on the Taskbar; I want two separate windows like in X...

How to stop outlook resending attachments multiple times
When I send an email with an attachment, it will send several times before I manually have to stop it. Does anybody have any suggestions on how to rectify the problem? "fisco" <fisco@discussions.microsoft.com> wrote in message news:F793C1E7-AE4D-45F7-BCA6-98C570F2F915@microsoft.com... > When I send an email with an attachment, it will send several times before > I > manually have to stop it. Does anybody have any suggestions on how to > rectify > the problem? Sorry, but this newsgroup is for questions about Access, the database product tha...

finding values in a sum
if you have an array of numbers and want to identify which of those numbers add up to a specified value, is there a function in excel that can help you to find the correct combination of numbers. eg in a simple example; if the array of numbers was 2,3,5,6 and the specified value was 9, we know the only combination of numbers from this array that would sum to give the value 9 are 6 & 3, however with a larger array of numbers (20) or more, it would be more difficult solve the problem manually. http://groups.google.com/groups?threadm=e3iWLUiYDHA.2960%40tk2msftngp13.phx. gbl See my previo...

Enable Double sided printing contiuously when printing multiple s.
When printing the 'entire workbook' I am not able to print continuously double sided. Each seperate sheet is an individual print item and so when the sheet has an odd number of pages the last sheet is blank I am not sure what you say but if the sheet has no data then it will not print. Excel does not print blanke sheets. >-----Original Message----- >When printing the 'entire workbook' I am not able to print continuously >double sided. Each seperate sheet is an individual print item and so when the >sheet has an odd number of pages the last sheet is blank &g...

Multiple Accounts held at the SAME Institution with DIFFERENT logons
I am trying to sync Money 2006 to my Banc of America Investment Services accounts. I have two accounts with two DIFFERENT logons, an IRA and a regular brokerage. Money will sync fine to one or the other, but I can't set it up to sync to both because when I click on Online Services it is already setup so I can't add a different login, etc. -- Daniel Blackmon Project Lead - Software Engineer Worldwide Environmental Products Inc. In microsoft.public.money, Daniel wrote: >I am trying to sync Money 2006 to my Banc of America Investment Services >accounts. I have two accounts w...

setting up an email acct. and fowarding it to multiple email addre
one user in our company want to setup a generic email xxxxx@abc123.com and wants to foward mutliple email accounts in the same company. well when i go into exchange users and computers and setup the account. under properties>exchange general>delivery options and foward address. it only allows to forward one address. so my question is how do i set it up to allow to foward like to 4 other email addresses within the same company? is this possible and how do we go about setting it up? btw...we have exchange 2k3 with outlook 2k. thx in advance. Is there a specific reason to crea...

sum of directories does not equal disk total?
I want to clean up my drive and to find out where all the usage was I did a "Properties"inquiry on all the directories. The sum of these is short of the total by 48 of 168 gig used on a 250 gig drive. How do I find out what is using the 48? Thanks Andy AndyM wrote: > I want to clean up my drive and to find out where all the usage was I did a > "Properties"inquiry on all the directories. The sum of these is short of the > total by 48 of 168 gig used on a 250 gig drive. How do I find out what is > using the 48? > Thanks > Andy AndyM wrote: ...

Adding multiple tables in one report
I am trying to customize the default report Daily Detailed Sales with Tax. What i need to do is add the Tender Type (Credit card / cash / check) as another column in the report. So far i have found out: i need to add a column i need to import the TenderEntry table for the data I need to find out: How to import another table I have tried to import the table using sql UNION function, but that wont work for me either. any help would be greatly appriciated. Thank You Hi ED, The things make sense to me regarding the adding tables and fields which you can do and customized the .grp file. ...

Named Ranges
I have a file with over 100 named ranges. I would like to expand the range of the name on all of these names. Is there a way to use the replace command or is manually the only way to do this? [This followup was posted to microsoft.public.excel.charting with an email copy to keith. Please use the newsgroup for further discussion.] If the names refer to hardcoded ranges, i.e., Name1 =Sheet1!$A$2:$A$5 then you will have to either fix them by hand or write a VBA program that will make the change(s) you need. Alternatively, you use named formulas that adjust as needed. Suppose you have ...

range color from date and database
newbie done some homework on excel i would like to have a range change color based on date and time and a name from a database--or any suggestion for example if it is thursday and the time is betweeen 1 and 3 pm i want the range to be say light red if out of tinme range i want it to be light green also the cell above the range as long as time constraints are met to have a name from a datasource any suggestions or ideas apprciated i need to have data from either a database or from say another sheet where user can enter a form the data from the datasource would fill range values a...

add multiple paychecks to budget
How can i add multiple paychecks to my budget? I'm using money '06. I tried adding another catagory in the income part of the budget, but it only allows for 1 paycheck. I want to track everything in the budget. Thanks What edition of Money? What version? If not Money Essentials (MEss), are you using the Advanced Budget (AB), Spending and Savings Budget (S&SB), or Essential Budget (EB)? "ShauntK" <ShauntK@discussions.microsoft.com> wrote in message news:CC0F5DCC-2E2A-4385-A4BD-02156C0D4994@microsoft.com... > How can i add multiple paychecks to my budget? I...