range checking #2

hi

i have a worksheet with a lot of data on it
column A contains the date
column B contains an area
column C contains the fault
column D contain a quantity


              A                        B                          C
D
           1/4/04                 stores                damaged
10
           7/4/04                 bins                    rust
60

what i want to do is    return  quantity for a date range(one week) that is
both damaged and in the stores or any other combination
there are 10 areas and 12 faults plus 52 weeks this is a lot of formulas

anyone got any ideas how i can
do this over a range


Thanks in advance


kevin





0
kev863 (17)
4/8/2004 5:22:21 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
685 Views

Similar Articles

[PageSpeed] 56

Hi
try something like
=SUMPRODUCT(--(A1:A100>=DATE(2004,4,1)),--(A1:A100<=DATE(2004,4,7)),--(
B1:B100="stores"),--(C1:C100="damaged"),D1:D100)

--
Regards
Frank Kabel
Frankfurt, Germany


kev carter wrote:
> hi
>
> i have a worksheet with a lot of data on it
> column A contains the date
> column B contains an area
> column C contains the fault
> column D contain a quantity
>
>
>               A                        B                          C
> D
>            1/4/04                 stores                damaged
> 10
>            7/4/04                 bins                    rust
> 60
>
> what i want to do is    return  quantity for a date range(one week)
> that is both damaged and in the stores or any other combination
> there are 10 areas and 12 faults plus 52 weeks this is a lot of
> formulas
>
> anyone got any ideas how i can
> do this over a range
>
>
> Thanks in advance
>
>
> kevin

0
frank.kabel (11126)
4/8/2004 5:32:15 PM
thanks Frank
this would mean i have to enter a formula for each condition
i have 10 areas and 12 faults for each week of the year
is there any way of reducing the number of formulas ?


"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:%23Udvv9YHEHA.4088@TK2MSFTNGP10.phx.gbl...
> Hi
> try something like
> =SUMPRODUCT(--(A1:A100>=DATE(2004,4,1)),--(A1:A100<=DATE(2004,4,7)),--(
> B1:B100="stores"),--(C1:C100="damaged"),D1:D100)
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> kev carter wrote:
> > hi
> >
> > i have a worksheet with a lot of data on it
> > column A contains the date
> > column B contains an area
> > column C contains the fault
> > column D contain a quantity
> >
> >
> >               A                        B                          C
> > D
> >            1/4/04                 stores                damaged
> > 10
> >            7/4/04                 bins                    rust
> > 60
> >
> > what i want to do is    return  quantity for a date range(one week)
> > that is both damaged and in the stores or any other combination
> > there are 10 areas and 12 faults plus 52 weeks this is a lot of
> > formulas
> >
> > anyone got any ideas how i can
> > do this over a range
> >
> >
> > Thanks in advance
> >
> >
> > kevin
>


0
kev863 (17)
4/8/2004 6:27:09 PM
Reply:

Similar Artilces:

How do I import a WORD file into EXCEL? #2
I'd like to import a WORD table to Excel but when I go to "Select Data Source", only other EXCEL files show, not my WORD files but the Help function indicates that all files should be showing. I've selected "Text Files" from the Data Source; I've tried "All File Types" with no luck. Anybody have a clue for me? ..signed, an Excel Idiot......thanks! File>open and select all files or copy and paste -- Regards, Peo Sjoblom "MauiMama" <MauiMama@discussions.microsoft.com> wrote in message news:27741FDC-23BA-478D-98AC-21760...

Average If Question #2
We are trying to average the numbers in one column only if they equal any or all of 6 different numbers from a different column (7474, 7921, 8612, 9011, 3945, 2209). We are using this formula, but it seems to be giving us slightly different numbers than if we just pulled the numbers together through sorting and took the average. =AVERAGE(IF(B1:B9={7474, 7921, 8612, 9011, 3945, 2209},C1:C9,FALSE)) Try: =AVERAGE(IF(B1:B9={7474,7921,8612,9011,3945,2209},C1:C9)) Array-entered, meaning press ctrl + shift + enter. HTH Jason Atlanta, GA >-----Original Message----- >We are trying to ...

How to return a range of values in a drop-down.
Is there a way to have Excel return a range of values in a drop down in one cell based on input from another cell. As an example: Cell A1 has the text "PVC Pipe" I want cell A2 to get input from A1, read through a table, and return the corresponding values (sizes) of PVC pipe. The results in A2 would be the following in a drop down: 4" 6" 8" If cell A1 contained the text "Metal Pipe" it would return values (sizes) of metal pipe from a table and not display the sizes of PVC pipe. 10" 12" 15" The bottom line and...

check marks
Is there a way to put a check in the spreadsheet? Several, put one is to put a character from the WingDing set something like =3Dchar(252) The format that cell only to *WingDings* Steve On Fri, 11 Aug 2006 19:18:01 +0100, Mary <Mary@discussions.microsoft.com= > = wrote: > Is there a way to put a check in the spreadsheet? -- = Steve (3) Mary Format cells to Marlett font and enter an "a"(no quotes) Gord Dibben MS Excel MVP On Fri, 11 Aug 2006 11:18:01 -0700, Mary <Mary@discussions.microsoft.com> wrote: >Is there a way to put a check in the spreadsheet...

Converting 4 page doc to 2 pages
I created a program in Publisher and sent to our printer. They called back and said the Xerox is not recognizing the format. Publisher has this formatted in 8.5 x 5.5. How do I put 2 pages onto one page so it will print? If you have setup this as a booklet, open a new instance of Publisher, setup the page as landscape, insert an additional page. Open your program, copy each page/paste to the new publication, two per page. -- Mary Sauer http://msauer.mvps.org/ "Pam" <Pam@discussions.microsoft.com> wrote in message news:9AD06459-5EC4-4F84-A881-380BF1F2652...

Using SUMIF to add data between a range of dates
Hi, I am developing a cashflow spreadsheet, and need to add a range of values (in column B) based on the criteria that they are relating to a set week, ie in column B has the amount to be paid, and column C has the date the amount is due. I need to find out the total amount due between 2 dates. Does anyone know how I can do this? Hi With start date in B20 and end date in B21 try this: =SUMIF(C2:C5,">=" &B20,B2:B5)-SUMIF(C2:C5,"<="&B21,B2:B5) Regards, Per "Jaspa" <Jaspa@discussions.microsoft.com> skrev i meddelelsen news...

damaged tracking logs #2
Running Exchange 5.5 SP3 on Windows NT 4.0 SP6a. I've recently started getting following error messages when attempting to search the tracking logs via the admin console: "The message tracking log file '\\<SERVERNAME>\Tracking.log\<DATE>.log' has a damaged entry at line <LINENUMBER>. Do you want to continue reading the file and attempt to recover the remaining entries?" <DATE> and <LINENUMBER> are always different. If I click YES, the search continues, at least until another error message appears. If I click NO, I get another dialog box s...

Out of Range
Hello, This is my VBA macro. I'm getting a "Subcription out of range #9" error message on: Windows(fname).Activate Can someone help fix it, if possible? Regards, fname = Application.GetOpenFilename Workbooks.Open filename:=fname Windows(fname).Activate Hi Jeff, Drop the line: > Windows(fname).Activate The opened workbook is the active workbook. --- Regards, Norman "Jeff" <Jeff@discussions.microsoft.com> wrote in message news:E599F406-0915-4F63-9244-822FA00994CE@microsoft.com... > Hello, > This is my VBA macro. I'm getting a "Subcripti...

Free web based bug tracking and timesheet #2
http://www.livetecs.com TimeLive Web Collaboration Suite is integrated suite for managing project life cycle including tasks, issues, bugs, timesheet, expense, attendance etc. - Free web based bug tracking - Free web based timesheet - Fully integrated project management and collaboration tool. - Configurable timesheet and expense management - Task management - Bug tracking - Issue Tracking - Employee Attendance - Free downloadable version - Free hosted (ASP) version (Unlimited space) - EMail notifications to keep you upto date TimeLive is developed using ASP.Net 2 and Microsoft Ajax 1.0 ...

Unable to delete cell range.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel New to excel and following a video tutorial. I am using auto-fill to create a range of numbers. The cells are selected and I try to delete all the content in the range by pressing the delete button per the tutorial but only the first cell in the range is deleted. Please help a newbie. Are you using a laptop or a condensed keyboard by any chance? If so, the key labeled 'delete' is the equivalent of 'backspace' & does delete only the content of the active cell in the range. You need to hold ...

Printing Checks #15
I am thinking about buying Money, but need to know how complex it is to set check printing. I was using QuickBooks Pro, but quit after buying the expensive check stock and failing three times to print checks. I use an HP5510 All-in-One inkjet printer. Has anyone been sucessful setting up check printing in Money? What version would you recommend (30 checks/month max, three bank accounts from the same bank - two companies, one personal) Thanks! -- jetcoots I print an average of four or five checks a month with very few problems. I use the laser wallet stock but will not go this way ...

Summing a range within a range
I need the formula that will sum a range within a range. Example: the primary range consists of the following numbers: 1,2,3,4,5,6,7,8,9,10 and I need to know how many numbers are >3 and <8. First I need to know the sum ot the qualifying numbers; second I need to know how many items there are. Stumped and exhausted...anyone's help will be greatly appreciated! -- Need to Know Try these... For the SUM: =SUMIF(A1:A10,">3")-SUMIF(A1:A10,">=8") For the COUNT: =COUNTIF(A1:A10,">3")-COUNTIF(A1:A10,">=8") -- ...

Sharing contacts #2
A while back I found some software that allowed sharing Outlook contact info. it used SQL and was fairly reasonably priced. I don't remember the name of the software or the web site. It was something like SQLContacts or ContactSQL or something like that. Does anybody know of that site or know of software that will work like that? TIA Brian ...

footer #2
Hi, Is it possible that if user creates new documents in word or excel, automatically (date, name, paths and etc) to be added as footer or is it possible somehow to make domainwide via AD? Regards, Ramin Thanks, Ramin In Excel, add the following code to Personal.xls Private WithEvents app As Application Private Sub app_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean) With Wb.ActiveSheet.PageSetup .LeftFooter = ActiveWorkbook.FullName .RightFooter = Format(Date, "dd mmm yyyy") End With End Sub Private Sub Workbook_Open() Set app = Ap...

Data Range
Hi there, I have searched through this forum cannot find a solution to what I need so here goes (or perhaps I have missed it). I have a workbook containing 11 worksheets - one for each country with the first worksheet containing a set of standard charts which are reused for each country. I am currently manually amending the data range and selecting the worksheet and highlighting the data for each country (so have to do this 10 times). The data is in the same location on each country worksheet so really the only amendment is the worksheet name. How do you recommend that I speed up t...

Display range name when a cell within the range is selected
How to display the name of a range when a single cell within that named range is selected. Kathy one way: This displays the named range(s) that the cell belongs to, if any, in the Status bar. Put this in the ThisWorkbook code module of your workbook. Private Sub Workbook_SheetSelectionChange( _ ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim sRanges As String Dim nmName As Name Dim bInRng As Boolean On Error Resume Next For Each nmName In ThisWorkbook.Names Debug.Print nmName.Name, nmName.Re...

Percentage on x axis and period range on y axis
Hi I'm trying to plot the length of time staff have worked for a company on the y axis in ranges i.e 0-6 months, 6-12 months etc. I have a seperate column that has converted the months worked into each of these catagories. I then would like to show on the x axis the percentage of staff that fall into each period. I would certainly appreciate any assistance! Wayne Wayne Beasley wrote: > Hi > > I'm trying to plot the length of time staff have worked for a company > on the y axis in ranges i.e 0-6 months, 6-12 months etc. I have a > seperate column that has converted t...

Access 2.0 to Access XP?
Hello, My customer has an Access 2.0 database which they would like to upgrade to Access XP. Can I just use decompile/recompile/compact? Is it necessary to take any other steps? Thanks in advanc. Tracy No you can't just do that. Converting from A2.0 to any later version may involve a major exercise. Beyond writing a book, which you can buy, tell us what specifically is your problem? If you don't know A2.0, ie the "from" AND the "to", then you are not qualified to convert it, is my impression. Having said that, conversions are not too difficult if you jus...

Counting Dates #2 #2
What formula to read a range of cells with a lot of dates in it and than count how many times the current month shows up in it (has to recognize the year too because if it only counts all dates with November in them it cant count 2003, 2004 etc.. only the current month) Example: 11/4/2005 11/9/2003 11/12/2005 12/1/2005 This would need to give me a total of: 2 Thank you, -- na Hi! Try one of these: =SUMPRODUCT(--(TEXT(A1:A4,"mmmyyyy")="Nov2005")) OR: B1 = 'Nov2005 =SUMPRODUCT(--(TEXT(A1:A4,"mmmyyyy")=B1)) OR: =SUMPRODUCT(--(MONTH(A1:A4)=MONTH(TO...

merging files #2
Hi My wife has a number of separate publisher files that she needs to combine into a single file. There doesn't seem to be an obvious way to do this.. I have looked at the "insert" menu and it looks like you can insert a blank page but not an existing fileā€¦ How do I combine multiple publisher files into one document? Thanks Michael mlsred <mlsred@discussions.microsoft.com> was very recently heard to utter: > How do I combine multiple publisher files into one document? Copy and Paste. -- Ed Bennett - MVP Microsoft Publisher On Fri, 6 Jan 2006 15:20:03 +0000,...

How do you point to a named range in linked workbooks?
Workbook #1 has a range named "Gross Sales" in Sheet1 Cells A2:K2. How can I point to this range so that I can link it to Sheet1 Cells2:K2 in Workbook #2? Is this what you're looking for...? In workbook 2 enter "=[Book1]Sheet1!$A$2" in cell A2. Do the same for cells B2 through K2 with the correct respective links. Hope this helps. -Chad "KG" wrote: > Workbook #1 has a range named "Gross Sales" in Sheet1 Cells A2:K2. How can I > point to this range so that I can link it to Sheet1 Cells2:K2 in Workbook #2? Or, if you don't like to ...

"check store" button not appearing
I have two stores, one of which has a check stores button, and another that doesn't. I've looked through the newsgroups, and have seen that this has been a problem before. The thing is, I've checked the settings they say to change: "display find window" set to off if you want the check stores button. This has shown up at one store after changing this setting, but the other store didn't show the button. Does anyone know of anything else you can do to make the button appear? In Administrator > configuration HQ Client tab Set the server name to the server I...

exporting to excel #2
I've got outlook 2002 and it won't export to excel. It freezes up and stops. Any suggestions? Thanks. ...

offline sources in DPM 2007 SP1 ver 2.0.8851.0
I'm looking for some help troubleshooting an issue when backing up my Exchange databases where the error I get is "The operation failed for Storage group on exchange server because the data source is not available. ID 30169 Details: Unknown error Then the alert goes on to tell me to verify that the data source is online, is not in a restoring state...etc. I'm at a lost to believe that one of my exchange databases is offline because we have plenty of other monitors that would be letting us know. How do I go about troubleshooting this? Is there a latency issue...

Checked out enterprise calendar in Project Server 2007
Hi: I have a checked out enterprise calendar, how can I find out who checked out? Is it possible to force it to be checked in? Cheng Wu Cheng Wu -- Log into PWA with administrator permissions and then click Server Settings - Force Check-In Enterprise Objects. Click the pick list at the top and choose the Enterprise Calendars item. On this page, you will now see who checked out the calendar and when they checked it out. And then you can also check in the calendar as well. Hope this helps. -- Dale A. Howard [MVP] VP of Educational Services msProjectExperts h...