how to add those numbers?

[Mark]

I have a column of numbers that I have to sum up,
but there is one problem: some of them are formatted as a regular
number (sometimes with decimals) and some are formatted as time
(8:30 etc.)
the regular sum(a:a) formula does not work on all of them
is there any way to do it and get the result either in decimals or
time or I'll have to redo the whole thing?

Mark

 

[Dave Peterson]

I think I'd get all those numbers to a common unit (time or minutes or hours).

If you have 30 in a cell (say A1) and it represents 30 minutes, you can use a
helper cell and put a formula like:

=A1/24/60

(divide by the number of hours in a day then divide by the number of minutes in
an hour.)

Format that cell as time to see it nicely.

Then you can use =sum()
and use a custom format of:

[hh]:mm:ss

And you'll see your sum in total hours, minutes, seconds.

 

[Bob Phillips]

If, and I accept that this might be a big If, all the non-time numbers are >
1 you could use

=SUMIF(A:A,"<=1")*24+SUMIF(A:A,">1")

 

[Mark]

If, and I accept that this might be a big If, all the non-time numbers are >
1 you could use

=SUMIF(A:A,"<=1")*24+SUMIF(A:A,">1")

thanks Bob, this works perfectly
I only have one question:
what those quotation marks do in this formula?

Mark

 

[Bob Phillips]

Hi Mark,

That is a good question.

If you were testing for a straight equals a value, you would just use say

=SUMIF(A:A,1)

But if you want to test for less than, you would think you could use

=SUMIF(A:A,<1)

but you cannot, you have to wrap it with quotes which SUMIF evaluates

=SUMIF(A:A,"<1")

It gets more interesting if the value you want to compare is in another cell
say B1. Straight equals comparison

=SUMIF(A:A,B1)

Less than comparison

=SUMIF(A:A,"<"&B1)

because the formula needs to get the value from B1, not the text B1, and the
& is used to concatenate to a text value.

 

[Mark]

Hi Mark,

That is a good question.

If you were testing for a straight equals a value, you would just use say

=SUMIF(A:A,1)

But if you want to test for less than, you would think you could use

=SUMIF(A:A,<1)

but you cannot, you have to wrap it with quotes which SUMIF evaluates

=SUMIF(A:A,"<1")

It gets more interesting if the value you want to compare is in another cell
say B1. Straight equals comparison

=SUMIF(A:A,B1)

Less than comparison

=SUMIF(A:A,"<"&B1)

because the formula needs to get the value from B1, not the text B1, and the
& is used to concatenate to a text value.

this was very educational,
thank you very much Bob

Mark