• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

summing time

Lawrence Dodge

Lawrence Dodge

Member
Hi all--

I have a spreadsheet that calculates down time on a machine (G#-F#). The time is in H# and the reason is in for the down time is in I#. I tried =SUM(IF(I5:I60="Heaters",(H5:H60),"")) to figure out the total time the machine was down due to the heaters, but it didn't work. I would like to be able to have a total time for each of the reasons (about 18)--I have the total time of everything combined, but how do I get total times per reason?
 
A PivotTable would give you a nice report of every reason, but to correct the syntax of your formula:

=SUMIF(I5:I60,"Heaters",H5:H60)


If you had a list of all the possible reason somewhere, you could amend this formula with a cell reference to make it easier to copy, ala:

=SUMIF($I$5:$I$60,A2,$H$5:$H$60)
 
Ok Prasaddn -- now that you reminded me of Pivot tables, can you remind me how to change which data is used for the chart. I tried to right click the chart like I do with charts I built, but that didn't work. I update the charts weekly so this could be very helpful:)

LCD
 
The PivotChart is tied directly to the PivotTable. You need to edit/refresh the PivotTable if you need different data. You can only change the filter settings/formats on the chart.
 
Hello LCD.. Hope you are able to get the pivot graphs the way you wanted. Just remember to custom format the sum of total time field to [h]:mm:ss
 
Hi Luke-- How do I edit the pivot table when I made a pivot chart directly?

LCD

thanks prasaddn for the hint about the time format
 
As far as I know, even when you make the chart directly, XL has to create the PivotTable somewhere...
 
somewhere over the rainbow.... yeah I figured but where is the table? There is a chart next to the graph that has Count of Total, but I can't figure out how to change that.
 
I think that is on the right track, I still am struggling with the time format, but I will keep plugging away at it. Thanks again all

LCD
 
You must log in or register to reply here.
Back
Top