A B C

1/9/08 7
1/9/08 8 7
1/9/08 3 3
1/10/08 5
1/10/08 10 7

If column A and B are my data and I use the following formula to calculate
the hours of OT for each day =SUMIF($A$2:$A2,A2,B$2:B2)-8 to get the totals.
How do I get it to only give me the hours over 8 for each day and not the
totals.
With the above formula I am getting -1, 7, 10, -3, 7 I need to get the
totals as above.

Any suggestions

In C2:
=IF(SUMIF($A$2:$A2,A2,B$2:B2)8,SUMIF($A$2:$A2,A2, B$2:B2)-8,"")

In D2:
=IF(SUMIF($A$2:$A3,A3,B$2:B3)8,SUMIF($A$2:$A3,A3, B$2:B3)-8-SUM(C2),"")
Copy D2 down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Griffey5" wrote:

A B C

1/9/08 7
1/9/08 8 7
1/9/08 3 3
1/10/08 5
1/10/08 10 7

If column A and B are my data and I use the following formula to calculate
the hours of OT for each day =SUMIF($A$2:$A2,A2,B$2:B2)-8 to get the totals.
How do I get it to only give me the hours over 8 for each day and not the
totals.
With the above formula I am getting -1, 7, 10, -3, 7 I need to get the
totals as above.

Any suggestions

Pl dismiss the earlier suggestion
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

This seems to do what you want:

Enter this formula in C2:

=MAX(0,B2-8)

Enter this formula in C3 and copy down as needed:

=MAX(SUMIF(A$2:A3,A3,B$2:B3)-8-SUMIF(A$2:A2,A3,C$2:C2),0)

Some of these formulas will return zeros. If you want to hide them one way
is to use a custom number format:

Select the entire range of formulas
Goto the menu FormatCellsNumber tab
Select Custom and enter this code in the little box below where it says
Type:

General;General;;@

OK out

Note that the cells will still contain a numeric 0, you just won't see them.
You should be aware of that if you need to do further calculations on this
data where zeros may have to be accounted for.

--
Biff
Microsoft Excel MVP


"Griffey5" wrote in message
...

A B C

1/9/08 7
1/9/08 8 7
1/9/08 3 3
1/10/08 5
1/10/08 10 7

If column A and B are my data and I use the following formula to calculate
the hours of OT for each day =SUMIF($A$2:$A2,A2,B$2:B2)-8 to get the
totals.
How do I get it to only give me the hours over 8 for each day and not the
totals.
With the above formula I am getting -1, 7, 10, -3, 7 I need to get the
totals as above.

Any suggestions