Can't SUM cells

[gz3s36]

I am trying to add times H1:H21 in H23. Any ideas?

[etaf]

they are not time values - they are text
you need to change to time values to get a number or at least a decimal part of a number - which is the way time is worked out
then you can sum
also format the sum cell to [H]:MM which will display more than 24

see attached i have added some columns to show

[gz3s36]

Can you concert the actual H column? I can't.

[etaf]

you maybe able to using VBA , I had to create the new column using =timevalue(H1)
i dont know VBA well enough

[etaf]

OR
create a column with timevalue(H1) and copy down
now copy that range
Paste Special - text only into H to get valid time values

[Pete_UK]

Select the cells H1:H21 then click on Data | Text-to-columns, then click Finish on the firs panel.

Hope this helps.

Pete

[Tony Valko]

Can you concert the actual H column? I can't.

Select the range H1:H21
Goto the Data tab>Text to Columns
Click Finish

[gz3s36]

So far so good. I wrote the macro to do the conversion and it worked. My next question is that I use this =SUMPRODUCT((((ISNUMBER(SEARCH("40",T2:T5000))+ISNUMBER(SEARCH("41",T2:T5000))+ISNUMBER(SEARCH("43",T2:T5000))+ISNUMBER(SEARCH("47",T2:T5000))+ISNUMBER(SEARCH("81",T2:T5000))+ISNUMBER(SEARCH("83",T2:T5000)))*(G2:G5000={"PM","PDM"})))*(P2:P5000="CLOSE")) for finding the number of workorders that are closed. How do I sum up the hours of closed workorders in column AG2:AG5000 that fit this formula?

[Pete_UK]

Where does your existing formula appear? (which cell, which sheet?)

Where do you want the new formula to go?

If the existing formula counts the number of orders, then this:

=SUMPRODUCT((((ISNUMBER(SEARCH("40",T2:T5000))+ISNUMBER(SEARCH("41",T2:T5000))+ISNUMBER(SEARCH("43",T2:T5000))+ISNUMBER(SEARCH("47",T2:T5000))+ISNUMBER(SEARCH("81",T2:T5000))+ISNUMBER(SEARCH("83",T2:T5000)))*(G2:G5000={"PM","PDM"})))*(P2:P5000="CLOSE"),AG2:AG5000)

(changes in red) should add up the times from column AG where those records occur. You might need to apply a custom format of [h]:mm:ss so that the sum does not wrap at 24 hours.

Hope this helps.

Pete

[gz3s36]

The formula was in AX6 (List of Workorders) sheet. I want to new one to go in AZ6 (same sheet). I would expect a result of approx. 1917 hrs.

[gz3s36]

I was wrong on my result set. Still trying to get the correct number.

[gz3s36]

The correct result set should be about 864 hrs.

[gz3s36]

Got it. Thank you everyone.