# Sum of time not working

#### Tarek_CTG

##### Board Regular
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??

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.

Last edited:
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:
Code:
``=MOD(A3,1)*B3``

Now you can use the normal SUM function.

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??

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:
Code:
``=MOD(A3,1)*B3``

Now you can use the normal SUM function.

Thanks it works greatly!!

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.

Replies
5
Views
56
Replies
7
Views
136
Replies
9
Views
144
Replies
1
Views
266
Replies
7
Views
151

1,196,206
Messages
6,014,011
Members
441,802
Latest member
Aneurysm

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back