# Sum Time Issue

• Hi,

Can someone help with this file please?
The Sum doesn’t work properly

For the Average, I got some assistance by a member here
=((HOUR(b42)*60+MINUTE(b42))/COUNT(b1:b40))/1440

I believe this works just fine, however, if something wants to suggest something else, please
However the most important thing now how to solve the Sum issue

Hint, the whole reason behind what’s happening the nature of the generated report from where I get my data, as the timing with the date there comes like 1/1/1970 9:02:00 AM for instance, and there will be no possibility to change this

## Files

• totals formula formatted as [h]:mm

Code
``=SUM(B1:B40)-SUM(COUNTA(B1:B40)*25569)``

because your source data contains the date 01/01/1970 which has a numeric value of 25569 which needs to be deducted from each time

## Files

• great
but there is a problem makes this formula doesn't work with me
that the range B1:B40 will get blank cells occasionally
like If I'll calculate the Sum for B1:B50 (10 empty cells there)
It'll not generate the needed results
I've tried sumif(b1:b50,<>"") but it doesn't work I don't know why

• Well, I figured it out
I believe I need to use this
=SUMIF(B1:B50,"<>")-SUM(COUNT(B1:B50)*25569)

I believe this sort the blanks issue
Thank you so much
You helped me a lot

• For help with formulae, you should post in the Excel Formulas section. Some people cant help with VBA, so will not look at any posts in that section - please choose the correct section in future.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!