Summing a Formula

  • Thread starter Thread starter Debbie
  • Start date Start date
D

Debbie

I have formulas in 5 columns that give me the total hours a child is
in daycare. The formula calulates the total time according to there
sign in and out times. That works great. But when I sum those
formulas, it says "0". What am I doing wrong?

=SUM(B47:K47)
 
Hello Debbie,

B47:K47 are 10 cells, not 5. Which cells are containing your data and
how are they formatted (integer numbers, date / time format, ...)?

Can you show some sample data?

Regards,
Bernd
 
Debbie

Show us the formula that calculates the times. One suggestion to begin with
is format the cell with this formula in as [h]:mm

Mike
 
I have formulas in 5 columns that give me the total hours a child is
in daycare. The formula calulates the total time according to there
sign in and out times. That works great. But when I sum those
formulas, it says "0". What am I doing wrong?

=SUM(B47:K47)
Click to expand...

This is the formula I have that calculates the time in attendance if
this helps. Those results are what I am trying to sum.

=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46="A","2:00",TEXT(I46-
H46,"h:mm"))))
 
Hello Debbie,

B47:K47 are 10 cells, not 5. Which cells are containing your data and
how are they formatted (integer numbers, date / time format, ...)?

Can you show some sample data?

Regards,
Bernd
Click to expand...

I have a formula in cell B47, then the B48 is blank and so on.

This is the formula in the 5 cells.

=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46="A","2:00",TEXT(I46-
H46,"h:mm"))))
 
I have a formula in cell B47, then the B48 is blank and so on.

This is the formula in the 5 cells.

=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46="A","2:00",TEXT(I46-
H46,"h:mm"))))
Click to expand...

The cells that have the formula in it are as a Text. The result that
is in the cell is 2:00 for example.
 
Debbie,

your trying to sum text values, try this

=IF(H46="s",TIME(2,0,0),IF(H46="CLOSED",TIME(0,0,0),IF(H46="A",TIME(2,0,0),I46-H46)))

Format this and the sum cell as [h]:mm

Mike
 
Hi Debbie,

Try with timevalue() instead of string formatted time:

"02:00" => timevalue("02:00")

I see a second issue, you try to 'sum' strings with I46-H46.

Wkr,

JP



I have formulas in 5 columns that give me the total hours a child is
in daycare. The formula calulates the total time according to there
sign in and out times. That works great. But when I sum those
formulas, it says "0". What am I doing wrong?

=SUM(B47:K47)
Click to expand...

This is the formula I have that calculates the time in attendance if
this helps. Those results are what I am trying to sum.

=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46="A","2:00",TEXT(I46-
H46,"h:mm"))))
 
Debbie,

your trying to sum text values, try this

=IF(H46="s",TIME(2,0,0),IF(H46="CLOSED",TIME(0,0,0),IF(H46="A",TIME(2,0,0),­I46-H46)))

Format this and the sum cell as [h]:mm

Mike



This is the formula I have that calculates the time in attendance if
this helps. Those results are what I am trying to sum.
Click to expand...
=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46="A","2:00",TEXT(I46-
H46,"h:mm"))))- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

The sign in and out times are formated as time h:mm AM/PM
H46: 3:00 PM I46: 5:10 PM

Then the formula I have to give the difference between the 2 times to
get the total hours attended is:
=IF(H46="s","2.00",IF(H46="CLOSED","0.00",IF(H46="A","2.00",TEXT(I46-My answer is Total Hours: 2.10 (2 hours, 10 min)

Then my third formula must sum the total hours attended.



The formula you gave me does not work. It just shows the formula in
cell.
 
Debbie,

your trying to sum text values, try this

=IF(H46="s",TIME(2,0,0),IF(H46="CLOSED",TIME(0,0,0),IF(H46="A",TIME(2,0,0),­I46-H46)))

Format this and the sum cell as [h]:mm

Mike



This is the formula I have that calculates the time in attendance if
this helps. Those results are what I am trying to sum.
Click to expand...
=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46="A","2:00",TEXT(I46-
H46,"h:mm"))))- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you! This worked. You guys are great!
 
Debbie,

your trying to sum text values, try this

=IF(H46="s",TIME(2,0,0),IF(H46="CLOSED",TIME(0,0,0),IF(H46="A",TIME(2,0,0),­I46-H46)))

Format this and the sum cell as [h]:mm

Mike



This is the formula I have that calculates the time in attendance if
this helps. Those results are what I am trying to sum.
Click to expand...
=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46="A","2:00",TEXT(I46-
H46,"h:mm"))))- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

One more question. My sum column, How do i round the answer to the
nearest 1/4 hour?
 
Hello,

Thanks, Biff, you are right.

=ROUND(SUM(--B46:F46)*24*4,0)/24/4
array-entered gives the sum rounded to a quarter of an hour.
To array-enter enter with CTRL + SHIFT + ENTER, not only ENTER. Curly
brackets will appear around your formula...

Regards,
Bernd
 
Hello,

Thanks, Biff, you are right.

=ROUND(SUM(--B46:F46)*24*4,0)/24/4
array-entered gives the sum rounded to a quarter of an hour.
To array-enter enter with CTRL + SHIFT + ENTER, not only ENTER. Curly
brackets will appear around your formula...

Regards,
Bernd
Click to expand...

Thank you! All formulas are working great. I love all you do for all
of us. You help to make our lives so much easier. I am learning more
and more about formulas. Soon, excel will be doing all my work. I will
only have to enter referring data. How awesome is that. Again, thank
you!
 
Back
Top