How to add time from multiple cells & date Cells & machine type c

I'm trying to add up the amount of machinery downtime from a number of 
different cells but the thing is that I need to only have it added up by the 
date set in another cell (Which can have multiple cells of the same date) as 
well as the specific name of that machinery in another cell(which can also 
have multiples of the same machinery name). How can i get to just add up for 
that specific date, machine name, and time.

Example: 

Date (Cell 1A thru 50A)  Machinery Name(Cell 1B thru 50B)  Downtime(Cell 1J 
thru 50J)

Please help
Thanks

0
Utf
1/28/2010 4:30:04 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
555 Views

Similar Articles

[PageSpeed] 57

Example:Sheet1 Data

Column A        Column B          Column C
1/26/2010       Machine #1        0:30
1/26/2010       Machine #1        1:45
1/26/2010       Machine #2        0:45
1/26/2010       Machine #2        3:00
1/26/2010       Machine #1        0:30
1/26/2010       Machine #2        1:30
1/27/2010       Machine #1        2:00
1/27/2010       Machine #1        4:30
1/27/2010       Machine #2        3:45
1/27/2010       Machine #2        2:15
1/27/2010       Machine #1        0:45
1/27/2010       Machine #2        1:00

Example:Sheet2 Data for Chart By Date

                       1/26/2010    1/27/2010   Etc.
Machine #1         Total            Total
Machine #2         Total            Total

The formula that I have is
=SUMIF(Sheet1!B1:B8,"Machine #1",Sheet1!C1:C8)

But this gives me the same total amount of downtime for all the dates and I 
only need the total time for one date in particular for each cell that the 
total downtime is going into. It takes to long doing this formula over and 
over collecting the data beacuse i have tons on machine names.

Please help
Thanks
 
"Peter Gonzalez" wrote:

> I'm trying to add up the amount of machinery downtime from a number of 
> different cells but the thing is that I need to only have it added up by the 
> date set in another cell (Which can have multiple cells of the same date) as 
> well as the specific name of that machinery in another cell(which can also 
> have multiples of the same machinery name). How can i get to just add up for 
> that specific date, machine name, and time.
> 
> Example: 
> 
> Date (Cell 1A thru 50A)  Machinery Name(Cell 1B thru 50B)  Downtime(Cell 1J 
> thru 50J)
> 
> Please help
> Thanks
> 
0
Utf
1/28/2010 6:45:01 PM
Hi,

You may use the following formula

=sumproduct((Sheet1!$B$1:$B$8=$A2)*(Sheet1!$A$1:$A$8=B$1)*(Sheet1!$C$1:$C$8))

Format the cell as [hh]:mm

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Peter Gonzalez" <PeterGonzalez@discussions.microsoft.com> wrote in message 
news:AC21B05C-7C60-4FF3-BF36-26D5859F1183@microsoft.com...
> Example:Sheet1 Data
>
> Column A        Column B          Column C
> 1/26/2010       Machine #1        0:30
> 1/26/2010       Machine #1        1:45
> 1/26/2010       Machine #2        0:45
> 1/26/2010       Machine #2        3:00
> 1/26/2010       Machine #1        0:30
> 1/26/2010       Machine #2        1:30
> 1/27/2010       Machine #1        2:00
> 1/27/2010       Machine #1        4:30
> 1/27/2010       Machine #2        3:45
> 1/27/2010       Machine #2        2:15
> 1/27/2010       Machine #1        0:45
> 1/27/2010       Machine #2        1:00
>
> Example:Sheet2 Data for Chart By Date
>
>                       1/26/2010    1/27/2010   Etc.
> Machine #1         Total            Total
> Machine #2         Total            Total
>
> The formula that I have is
> =SUMIF(Sheet1!B1:B8,"Machine #1",Sheet1!C1:C8)
>
> But this gives me the same total amount of downtime for all the dates and 
> I
> only need the total time for one date in particular for each cell that the
> total downtime is going into. It takes to long doing this formula over and
> over collecting the data beacuse i have tons on machine names.
>
> Please help
> Thanks
>
> "Peter Gonzalez" wrote:
>
>> I'm trying to add up the amount of machinery downtime from a number of
>> different cells but the thing is that I need to only have it added up by 
>> the
>> date set in another cell (Which can have multiple cells of the same date) 
>> as
>> well as the specific name of that machinery in another cell(which can 
>> also
>> have multiples of the same machinery name). How can i get to just add up 
>> for
>> that specific date, machine name, and time.
>>
>> Example:
>>
>> Date (Cell 1A thru 50A)  Machinery Name(Cell 1B thru 50B)  Downtime(Cell 
>> 1J
>> thru 50J)
>>
>> Please help
>> Thanks
>> 
0
Ashish
1/28/2010 11:46:45 PM
Hi,

You may also create a pivot table with column B in the row area, column A in 
the column area and column C in the data area.

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Peter Gonzalez" <PeterGonzalez@discussions.microsoft.com> wrote in message 
news:AC21B05C-7C60-4FF3-BF36-26D5859F1183@microsoft.com...
> Example:Sheet1 Data
>
> Column A        Column B          Column C
> 1/26/2010       Machine #1        0:30
> 1/26/2010       Machine #1        1:45
> 1/26/2010       Machine #2        0:45
> 1/26/2010       Machine #2        3:00
> 1/26/2010       Machine #1        0:30
> 1/26/2010       Machine #2        1:30
> 1/27/2010       Machine #1        2:00
> 1/27/2010       Machine #1        4:30
> 1/27/2010       Machine #2        3:45
> 1/27/2010       Machine #2        2:15
> 1/27/2010       Machine #1        0:45
> 1/27/2010       Machine #2        1:00
>
> Example:Sheet2 Data for Chart By Date
>
>                       1/26/2010    1/27/2010   Etc.
> Machine #1         Total            Total
> Machine #2         Total            Total
>
> The formula that I have is
> =SUMIF(Sheet1!B1:B8,"Machine #1",Sheet1!C1:C8)
>
> But this gives me the same total amount of downtime for all the dates and 
> I
> only need the total time for one date in particular for each cell that the
> total downtime is going into. It takes to long doing this formula over and
> over collecting the data beacuse i have tons on machine names.
>
> Please help
> Thanks
>
> "Peter Gonzalez" wrote:
>
>> I'm trying to add up the amount of machinery downtime from a number of
>> different cells but the thing is that I need to only have it added up by 
>> the
>> date set in another cell (Which can have multiple cells of the same date) 
>> as
>> well as the specific name of that machinery in another cell(which can 
>> also
>> have multiples of the same machinery name). How can i get to just add up 
>> for
>> that specific date, machine name, and time.
>>
>> Example:
>>
>> Date (Cell 1A thru 50A)  Machinery Name(Cell 1B thru 50B)  Downtime(Cell 
>> 1J
>> thru 50J)
>>
>> Please help
>> Thanks
>> 
0
Ashish
1/28/2010 11:48:05 PM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

multiple flyers per page?
Hello. I need to make a custom template with publisher. It will be one 8 1/2 by 11 paper divided horizontally into three parts so that there are three fliers that can be printed. each flier needs to be double printed so I need to make it a double sided project like a postcard or brochure layout. I can figure out text and image stuff. But I don't know how to make a basic custom project that is divided into three panels horizontally (so 8 inches by 3.83 inches roughly) Also an additional customization would divide each 3.83 by 8 inch flyer into two parts so one 8 by 11 page would ...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

Removing multiple users from Outlook
We did some computer switching, which involved uninstalling a copy of Office XP and replacing it with a copy of Office 2003. Now when I open Outlook, it makes me choose a user. This wasn't in the .pst file which was transferred for the other computer, but apparenly it was on this computer someplace. How can I get Outlook to simply open for the one account without that dialog box popping up? Aloha Annie, Control Panel | Mail | Show Profiles...remove any profiles you don't want. Select the one you do want and set it as the default. (if there are more than one left) -Ben-...

Multiple stacked bar charts
I have a project where i have to chart the compliance of three groups over a number of months. So for each data set there is a Compliance#1 stacked upon NonCompliance#1, then Compliance#2 stacked upon NonCompliance#2 and finally, you guessed it, Compliance#3 stacked upon NonCompliance#3. When i design a chart in excel it wants to place everything on top of each other producing one column of six not the required three columns of two for each week in the display. Can anyone point me i the right direction for this one?? Hi you can achieve this effect by having two layers in your x axis the at...

Add an Active Directory Object to Organizational Unit !!!!
Hi everyBody , I want to add an Active Directory Object (User Acount , Group , Compturer...) to an OU by using PowerShell , and i don't know How , any help will be apreciate . thanks . use the redirusr or redircmp from microsoft. It will always go to that OU. "mamhil" <mohamedtawfik@hotmail.com> wrote in message news:BAC9EB20-A4B6-466B-96A9-587B645A18F5@microsoft.com... > Hi everyBody , I want to add an Active Directory Object (User Acount , > Group , Compturer...) to an OU by using PowerShell , and i don't know How > , any help will be ...

Sort by Credit Card Type on EDC Detail Report
What do I have to do to get my EDC Detail Report to show different credit card types, like Visa, MC or Amex. Now under Tender Type, it just shows Credit card...well...I kinda knew that. How do I fix it? add tender types for each credit card type rather than just "credit card". then z out. all transactions after this change will reflect what you want in the reports. "tl" <tl@discussions.microsoft.com> wrote in message news:A54EEC95-208C-4962-A089-84B22A67632A@microsoft.com... > What do I have to do to get my EDC Detail Report to show different credit >...

Oldest date for Duplicate Cust. #
I'm trying to get the oldest date associated with a customer number, and in the Cust# column, i'll have many duplications of the same customer number. Let's say A is "Date", and B is "Cust#". (I won't be able to allow my users to sort the data, so i'll need a formula that returns either the oldest date, or the cell which contains the oldest date.) Any help is much appreciated! Nevermind. I found it using Google/Groups. {=MIN(IF($B$1:$B$10=B1,$A$1:$A$10))} >-----Original Message----- >I'm trying to get the oldest date associated with a...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Dates #9
The problem of a date code... I need to address this so that fo example, 5/6/04 can be correctly entered as either 5th of June or 6t of May, depending from where the date emanted. regards -- Message posted from http://www.ExcelForum.com Couldn't you format the cell as mmmm dd, yyyy so that the user sees what date they entered in a non-ambiguous manner right away? Or maybe provide 3 inputs: Month, day, and year. You could combine them elsewhere. "adn4n <" wrote: > > The problem of a date code... I need to address this so that for > example, 5/6/04 can be c...

Multiple Simultaneous Outlook Sessions
Is there a way for Outlook 2007 or 2003 to have multiple Simultaneous Outlook Sessions open at one time? If so, how is it setup to send and receive from the same profile I would choose and save the replies back into the same profile? thx "Art" <Art@discussions.microsoft.com> wrote in message news:433A0AAA-9DB0-4411-954F-5165374B049D@microsoft.com... > Is there a way for Outlook 2007 or 2003 to have multiple Simultaneous > Outlook > Sessions open at one time? If so, how is it setup to send and receive from > the same profile I would choose and sa...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

Add PowerPoint 2007 to Office Basic
I have Office Basic installed on my laptop and need to add PowerPoint. What is the most recommended, easiest and least expensive way to do this? ...

how many receivers i can add
i want to use outlook send 2000 thousands emails to 2000 peoples at the same time. can i put them all in the recivers and send them at one time? thank you for you help ...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

date tracking
I am entering clients into a 2007 Excel spreadsheet. How do I make the entry turn color when 14 days have passed? Gene This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CAC8D4.5688AC60 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit As part of the "client" entries, do you enter the date the client was entered? This would be the key in doing this task. In a cell on the worksheet you could enter the formula for today's date like this =TODAY(). Then use the con...

printing multiple copies of Sigel GmbH
I have made a tent card in a small size of 5.588cm x 4.191cm for a swing tag, using Sigel GmbH in the "other labels" section. It prints one copy perfectly, leaving room on the page for at least about another 5 copies (which is what I want), but when in the print page section it doesn't give me the option to print multiple copies on the page. This will be an enormous waste of paper if I have to print the number of copies individually on each page. HELP! -- candere What is the product number for this label? I cannot find this size label on my 2007 version. -- Mary Sau...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

I want to add a mail account without setting a SMTP server. Is it possible?
I want to add a second mail account that is outside my company. Since the company firewall does not allow us to connect to SMTP-servers I want to add this account without setting the SMTP propertiy. I cannot specify the company server as it is an Exchange server and the outside one an IMAP one. Currently I have specified the outside SMTP server since outook does not allow me to add an account wihtout setting this. I keep getting annoying error messages as my computer can't connect to the outside SMTP. Any one know a way around this? cheers, mortb The company server has SMTP enabled, u...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

Multiple copies of email in "Forward to"contact
HIe, I am running Exchnage 2003. My CEO has a conatct in our organisation that refer to his blackberry contact. I have set the forwarding to also keep a copy in his mailbox. What happens is that three copies of his forwarded email hit his blackberry account! Only one hits his internal mailbox. What I ahve determined is that the front end Exchange server seems to be creating the additional copies. Is this how its meant to be (don think so cos its such a nuisance and my CEO keeps complaining about it). How do I tell the front end server that this guy's mail server is the one that...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...