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

[Peter Gonzalez]

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

 

[Peter Gonzalez]

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

 

[Ashish Mathur]

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

 

[Ashish Mathur]

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