Summing a Formula

[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)

 

[Bernd P]

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

 

[Mike H]

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

 

[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)

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]

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

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"))))

 

[Debbie]

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"))))

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

 

[Mike H]

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

 

[Bernd P]

Hello Debbie,

Use
=SUM(--B46:F46)
and format as [h]:mm

Regards,
Bernd

 

[JP Ronse]

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)

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]

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.

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

- Show quoted text -

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]

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.

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

- Show quoted text -

Thank you! This worked. You guys are great!

 

[Debbie]

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.

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

- Show quoted text -

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

 

[T. Valko]

=SUM(--B46:F46)

That would have to be array entered.

 

[Debbie]

=SUM(--B46:F46)

That would have to be array entered.

--
Biff
Microsoft Excel MVP

Hello Debbie,

Use
=SUM(--B46:F46)
and format as [h]:mm

Regards,
Bernd- Hide quoted text -

- Show quoted text -

Biff, How do I enter an array? Also, how would I make this formula
round to the nearest 1/4 of an hour?

 

[Bernd P]

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

 

[Debbie]

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

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!