Sum of data with two criteria

Hi there, i have a (simple!?) problem with the following..

In my sheet, i have 3 columns:

column A,  containing a order-number
column B,  containing a quota
column C,  containing a week-number


Now what needs to bee counted, is the SUM of the quota (column B)
occurences from a specific order, AND a specific week!

Problem is, the rows can contain multiple occurences of an
ordernumber...


I'm feeling quitte stupid, can anyone help me please? :confused:


---
Message posted from http://www.ExcelForum.com/

0
2/6/2004 1:25:50 PM
excel 39879 articles. 2 followers. Follow

3 Replies
542 Views

Similar Articles

[PageSpeed] 55

Hi
try
=SUMPRODUCT((A1:A1000=order_number)*(C1:C1000=week_number),(B1:B1000))

Frank

> Hi there, i have a (simple!?) problem with the following..
> 
> In my sheet, i have 3 columns:
> 
> column A,  containing a order-number
> column B,  containing a quota
> column C,  containing a week-number
> 
> 
> Now what needs to bee counted, is the SUM of the quota (column B)
> occurences from a specific order, AND a specific week!
> 
> Problem is, the rows can contain multiple occurences of an
> ordernumber...
> 
> 
> I'm feeling quitte stupid, can anyone help me please? :confused:

0
frank.kabel (11126)
2/6/2004 1:40:53 PM
One way:

Assume your order-number is in D1 and your week number is in cell D2.

    =SUMPRODUCT(--(A1:A1000=D1),--(C1:C1000=D2),B1:B1000)



In article <Simon.Zarus.117id0@excelforum-nospam.com>,
 Simon Zarus <<Simon.Zarus.117id0@excelforum-nospam.com>> wrote:

> Hi there, i have a (simple!?) problem with the following..
> 
> In my sheet, i have 3 columns:
> 
> column A,  containing a order-number
> column B,  containing a quota
> column C,  containing a week-number
> 
> 
> Now what needs to bee counted, is the SUM of the quota (column B)
> occurences from a specific order, AND a specific week!
> 
> Problem is, the rows can contain multiple occurences of an
> ordernumber...
> 
> 
> I'm feeling quitte stupid, can anyone help me please? :confused:
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
>
0
jemcgimpsey (6723)
2/6/2004 1:43:36 PM
Thanks Frank, this works fine!!   :)



Frank Kabel wrote:
> *Hi
> try
> =SUMPRODUCT((A1:A1000=order_number)*(C1:C1000=week_number),(B1:B1000))
> 
> Frank
> 
> > Hi there, i have a (simple!?) problem with the following..
> >
> > In my sheet, i have 3 columns:
> >
> > column A,  containing a order-number
> > column B,  containing a quota
> > column C,  containing a week-number
> >
> >
> > Now what needs to bee counted, is the SUM of the quota (column B)
> > occurences from a specific order, AND a specific week!
> >
> > Problem is, the rows can contain multiple occurences of an
> > ordernumber...
> >
> >
> > I'm feeling quitte stupid, can anyone help me please? :confused: 

--
Message posted from http://www.ExcelForum.com

0
2/6/2004 1:53:37 PM
Reply:

Similar Artilces:

Two network interface cards problem
Hi, In my machine,I have two network iterface cards.One for Internet,and the other for local area network.I want to bind the second card to send and receive data with UDP.I have try to use bind() function to bind its IP address,but failed because sometimes I can receive data.Who can help me,thanks in advance. bind() does not bind an IP address; it binds a port number, and "binding" means "publishes to the Internet as a connectable port number". There have been previous discussions of how to make a port available only on a particular network segment, but as far as I kn...

"And" "Or" Search Criteria
I have a field that contains summaries of articles. I want to search within the field for the following: "Red" and "Blue" or "Green" and Blue" I want my results to return all articles that contain both "red" and "blue" in an article and all articles that contain both "green" and "blue" in an article. I can easily return all articles that contain "red" or "green" but the "AND" function is causing me to return ZERO articles which I know is incorrect. Thanks for all of your help in advance. ...

restrict data in combo box
I have a combo box that lists returns. The returns can be open or closed. I want the user to be able to restrict the data in the combo box if they check the [ckOpen] check box. I've tried several ways but none of them work. Any help would be appreciated. Combo box query: SELECT tReturns.MerchandiseReturnNo, tReturns.VendorName, tReturns.VendorID, tReturns.Description, tReturns.ReturnDate, tReturns.COGAccountNo, tReturns.ReturnAmount, tReturns.Status, tReturns.CompanyID, tReturns.MerchandiseReturnNo, IIf([Forms]! [fReturns]![ckOpen]=True,[Status]="Open","*") AS ReturnOp...

How do I identify Filter criteria or variable graph title?
I have a graph at the top of a sheet. In line 20 starts data with a filter on the data elements. When I set a filter on A20, the data below is filtered and the graph above is changed. However, I want the graph title to reflect the filter selection criteria. Is this possible? ...

Combining data from multiple sheets
I have been using OmniPage 12 to scan columnar data and then export it to Excel. So far I have had reasonable success with the OCR formatting but when exporting the data, each scanned sheet becomes a sheet in Excel. I want all the data on one sheet so that I can manipulate it (sort, pivot, etc.). I have had no luck with help at the OmniPage bulletin board so I thougt, alternatively, is there a way to combine data from multiple sheets that would be simpler than copy/paste. Hi Ron - Have you tried going from the other direction? If OmniPage allows you to save the files as Text (I beli...

Pivot Table Report: Getting average of difference of two date columns
Hello I have data in excel sheet as: WORK_ID PROJECT START_DT END_DT 1001 P1 1/1/2006 1/10/2006 1002 P1 1/25/2006 2/20/2006 1003 P1 3/15/2006 3/20/2006 I want to have a report that will give me average time taken to finish a work in a project. That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report ...

How do I place two or more charts on a worksheet?
I can't figure out how to keep Excel 2007 overlaying a new chart on top of an existing chart in a seperate worksheet. What I would like to do is create one 8.5 x 11 worksheet with four 3 x 5 charts on it. Select each chart and move it into position. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Rick@Draper" <Rick@Draper@discussions.microsoft.com> wrote in message news:082C5930-64F0-4A6E-958E-967BDCCDB9C9@microsoft.com... >I can't figure out how to keep Excel 20...

Error in set up data source
I'm using Windows XP and Office 2K. I tried to set up a data source connecting to an Acces table. When I picked the fields to be included in my query, I got the 'Syntax error in FROM clause' error prompt. The same resulted for connecting to an Excel table but there was no such problem for connection to a dBase (.dbf) file. Any idea? -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 100,000 Newsgroups - 19 Different Servers! =----- ...

messy data
Hi All, I have a problem with splitting some messy data and was hoping that someone could help me! I have a field which looks like this below: category6-category5-category4-category3-category2-category1 which I want to turn into this below, but in there own seperate columns: A B C D E F category1 category2 category3 category4 category5 category6 NOW, the problem is that sometimes the field doesn't go all the way to category6, it can go down to as little as category1 or category2 so would look like the below: category2-cate...

Get data out of a dataset.
Hi all! I use a dataset.ReadXML() to fill a dataset with data. With this I get different tables and relations availible in my dataset. What I�m wondering is: wich is the easy way to go here if I want to get specific values with "childvalues" via relations, out of this. Like a kind of sql:s "join" or something. /Marre Just an idea... if you fill a DataSet and then save the contents to disk as a Diffgram, using: DataSet.WriteXml(fileName, XmlWriteMode.Diffgram), you can see what the XML that is required to create the keys and relations looks like. ...

including dblink in a query to retrieve data
I connect to a table using a db link i want to execute an spl query and populate results in to a pivo table. My MS Query does not allow me to use a dblink and connect to th table. Please help -- Message posted from http://www.ExcelForum.com ...

MS Outlook 2007 mis-type exchange criteria
I have evidently mistyped some data for MS Outlook when setting up an exchange account during a new install. The application completed the first run of the install but when it restarted it can not get past the splash for Configuring Outlook. I do get a sign in dialog box but entering the user id and pw only errors pop up until the app closes down, There seems to be no way to get back into the account set up screens or anything else for that matter to try to correct the problem. I have uninstalled the suite and re-installed but evidently some of the original reg information is ret...

Exclusion List for Data Validation??
Hello! I have cells that require an entry (last names of people), so I have the validation set up for text >1 and <1000. This is good if the user decides to leave it blank, but if they put in a space, or two spaces, or more, or put in words like "none" "N/A" "unknown", etc, those responses are unacceptable. So how can I set up validation to allow for any entry except for blanks, spaces, or a list of words that I will constantly have to adjust as users get more creative? Thanks! VR/ Lost It would be really difficult to try and trap *every possible* ille...

Formatting Col C based on data in Col B
I have to format a report every day that is imported from SQL to Excel. My problem is that I am stuck on trying to "insert" text descriptions in Column C based on what is in Column B. The number of rows may vary from day to day (ie: one day the report is 315 rows and the next it may be 278 or 480). So, the total range of Col B would extend from (B2:end) on any given day. In plain language, If any of the data in Range (B:B) begins with "ML*" insert UPPERCASE "ABC" in Col C2 or If any of the data in Range(B:B) begins with "W*" insert UPPERCASE ...

Summing over multiple worksheets
Hi. I have weekly data by Contract number over multiple worksheets. I want to sum the hours associated with the contract. The contract information will not always be in the same cell, as new contracts are constantly added. My question is, should I use an If and then a SumIf or would a SumIf by sufficient? =SUMIF("'Apr 4:Jun 27'!, A5:A33",A5,"'Apr 4:Jun 27'!, G5:G33") I'm obviously missing something here. I want this formula to look in worksheets Apr 4 to Jun 27 in cells A5 through A33 and if there is a match to cell A5 on my summary...

Two formulas in on cell based on two numbers in another cell?
Hi, Not sure this is possible but...I have a cell that has a number range in it and based on an amount in another cell want to calculate a new range. For example: Initial Range: A1 = 10 - 12 Calc Amount: A2 = 5 Final Range: A3 = 50 - 60 I think I can get the results by concatenating two formulas I'm just not sure how to enter the original numbers (A1) or how to distinguish between the two in the final formula (A3) Using Excel 2003. Hope this makes sense. Thanks. I would put the range in two different cells (eg A1 and B1). Then the multiplication is easy. If you ...

Ghost Data/Large Operation
I have inherited a spreadsheet from a former coworker. In this spreadsheet, I cannot add or remove column(s)/row(s) without getting the "Large Operation" message indicating 'this is going to take forever to accomplish and are you sure you want to do this?' That's even for one column or row. I'm almost positive it has something to do with this sheet thinking that the last cell of data is EI1046599 even though there is no data in many thousands of rows and several dozen columns up to that point. My normal solution for this type of situation is to copy al...

MSCRM tools for data deduplication
Hi ppl, We are looking to use it for a potential MSCRM project for a large entreprise customer. The customer has multiple sources of customer data (over 5 databases), and what we are trying to achieve is to build a single source of customer data (ie, a customer master database in MSCRM). As there are multiple sources of customer data, there are many duplicate customer records. (eg, records could be in format such as Anthony Smith, Tony Smith, Toni Smith, etc). What we plan to do is to write some data conversion scripts using the MSCRM Data Migration Framework to load data from these datab...

Query to count between list of number (Predicting Start/End that may occur in data range)
Hi, I have a below list of numbers. 566667 566668 566669 566665 566666 566671 566672 566680 I want a query that would return a count between start and end of range. Like Start End Quantity 566665 566669 5 566671 566672 2 566680 566680 1 Thank you. On 2 apr, 07:17, Angela <ims...@gmail.com> wrote: > Hi, > > I have a below list of numbers. > > 566667 > 566668 > 566669 > 566665 > 566666 > 566671 > 566672 > 566680 > > I want a query that would return a coun...

Select data for plot
I want to create a simple line chart for each project; but need the ability for the user to not plot a particular project (Y 0r N) Proj1 Proj2 Proj3 Proj4 Y Y Y N 11.3% 8.5% 1.8% 0.9% 16.1% 12.4% 3.7% 1.5% 21.3% 16.6% 6.9% 3.9% Any ideas? Thanks Saintsman So far you have Proj1 through Proj4 Data. Add four more columns for Proj1 through Proj4 Chart. Assuming there is also a first column for some kind of categories or dates, Proj1 Data is column D, Proj1 Chart is column F, row 2 has the Y/N.... then put this formula in F3: =IF(B$2="Y",B3,NA()) fill this formula across to colu...

how can i build a three variable data table in excel ?
I know it is feasible but I can't figure out how to do it. Hi not really sure what you mean with this. Could you give an example? "Solario" wrote: > I know it is feasible but I can't figure out how to do it. Maybe?? dim myTable(1 to 2, 1 to 8, 1 to 17) as variant mytable(1,1,1) = "hi" mytable(1,1,2) = "there" ..... mytable(2,8,17) = "whew! that's a lot of entries" Solario wrote: > > I know it is feasible but I can't figure out how to do it. -- Dave Peterson ...

Data Modeling:Lookup table and Main table:establishing relationshi
I am working on creating data model from existing database using MS Visio 2007 Profesional Edition. Existing database is w/o PK-FKs & I am working to create relational DB which enforces RI. I have a lookup table which contains language codes,used by main table. The problem ,I am running into, is that these languagecodes(from lookup table) are used by 3 columns in main table. So, I am wondering how can I enforce PK-FK relationship here. As in... language_code from lookup table is PK and it has to associated w/ column(s) existing in main table. Something like following: Lookup Table ...

I did not save changes in Excel and now I lost data. Go Back?
I was asked if I wanted to save changes in Excel and I said no. Now I have lost valuable data. Can I go back and get those previous changes? If you opened a workbook and made changes then said "No" to save changes, you are out of luck. Start typing<g> Gord Dibben MS Excel MVP On Fri, 13 Jan 2006 12:34:01 -0800, "brian" <brian@discussions.microsoft.com> wrote: >I was asked if I wanted to save changes in Excel and I said no. Now I have >lost valuable data. Can I go back and get those previous changes? ...

Outlook 2003 asks for password two times
Hi All, i am using Outlook 2003 and one IMAP account is configured. everytime i start outlook it asks for the password two times. it really irritates the users. do anyone have any idea of why it is doing that. Thanks Regards akshat wrote: > > i am using Outlook 2003 and one IMAP account is > configured. everytime i start outlook it asks for the > password two times. it really irritates the users. Oh good! Someone else with the same problem. I believe I posted a question, but no reply. Same situation - Outlook 2003, one IMAP account, and it asks me the password twi...

Entering the same value into two table fields at the same time
Say that there is a table which has 2 fields (A, B) There is a form [Form1] (data entry type) to enter data for A by `TextBoxA`. There is also one more `TextBoxB` which is Not Visible. My target is to record the value of (entered thtough `TextBoxA`) into B field of the table as well. So I write down a code "Before Insert" of the Form1 that: " Form_Form1.TextBoxB = Form_Form1.TextBoxA " It is not recording A values into the field B... Where am I wrong? Can you help me please? Sory !!! When I write down the same code into `OnExit` of TextBoxA, it is working...!! yes, ...