Sum of time not working

[Tarek_CTG]

I have two columns. In A, there is date and time, and in B, some numbers.
I want to multiple the numbers of B column with the time of A column.
I used a formula in C column like this:
=text(text(A3,"[hh]:mm:ss")*B3,"[hh]:mm:ss")

Now, I got some values in column C which shows total hours, minutes and second.

Now, i want total hours, minutes and seconds of all values in column C. I have total 155 row data. So, i want to SUM of C3 to C157.

How can I do this as normal sum is not working??

Thanks in Advance.

 

[Snakehips]

Tarek_CTG,

You could try this Array formula...

=TEXT(SUM((A3:A157-INT(A3:A157))*B3:B157),"[hh]:mm:ss")

Confirm with Ctrl + Shift + Enter
NB it is independent of your sub-values in C

Hope that helps.

 

[MarcelBeug]

Hi,

Looks all quite strange to me. Your formula is not working: with the current date and time, TEXT(A3,"[hh]:hh:ss") result in "1011730:42:53" which can't be multiplied by a number (B3).

Instead, you'd better have time values in C, with cells formatted as [h]:mm:ss.
Formula in C3, copy down:

=MOD(A3,1)*B3

Now you can use the normal SUM function.

 

[Tarek_CTG]

Tarek_CTG,

You could try this Array formula...

=TEXT(SUM((A3:A157-INT(A3:A157))*B3:B157),"[hh]:mm:ss")

Confirm with Ctrl + Shift + Enter
NB it is independent of your sub-values in C

Hope that helps.

Thanks it works.

Now, i have another problem. In column D, i have some names like Hardware, Software, NCR, MUX. Now, i want to calculate the total sum for hardware, for software, for NCR, for MUX etc. How can I do that as my range is varying for each one??

Thanks in Advance.

 

[Tarek_CTG]

Hi,

Looks all quite strange to me. Your formula is not working: with the current date and time, TEXT(A3,"[hh]:hh:ss") result in "1011730:42:53" which can't be multiplied by a number (B3).

Instead, you'd better have time values in C, with cells formatted as [h]:mm:ss.
Formula in C3, copy down:

=MOD(A3,1)*B3

Now you can use the normal SUM function.

Thanks it works greatly!!

 

[Snakehips]

Re the totals per category in D.

Use COUNTIF along the lines of.... =SUMIF(D3:D157,"Hardware",C3:C157)

"Hardware" can be replaced with a cell reference if you wish.