What formula figures less than & greater than?
 

I need a formula that adds all hours 6 and under (for example: If the hours
are 5, 6, 7, 5, 8 then I want the sum to be 28 (5+6+6+5+6)). I also need a
formula that will then add all hours over 6 (for example: If the hours are 8,
7, 6, 9, 6, 8 then I want the sum to be 8 (2+1+0+3+0+2)).

What formula figures less than & greater than?
 

If your hours are in A1:A5

=SUM(IF(A1:A5<6,A1:A5,6))

and

=SUM(IF(A1:A56,A1:A5-6,0))

both confirmed with CTRL+SHIFT+ENTER


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=569678

What formula figures less than & greater than?
 

If your hours are in A1:A5

=SUM(IF(A1:A5<6,A1:A5,6))

and

=SUM(IF(A1:A56,A1:A5-6,0))

both confirmed with CTRL+SHIFT+ENTER


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=569678

What formula figures less than & greater than?
 

Kammy
A quick fix would be
Create a column next to the given values ( assume values in ColA, R1...Rx)
In Col B1 enter =IF(A1<7,MOD(A1,7),6)
and copy this formula down as far as is required.
Then Sum Col B with the formulas
If necessary the column could be hidden and the sum formula placed in Col A
For the second part - same principle but the formula would be
=MOD(A1,6)

Hope this is clear and helps

--
Don C


"Kammy" wrote:

I need a formula that adds all hours 6 and under (for example: If the hours
are 5, 6, 7, 5, 8 then I want the sum to be 28 (5+6+6+5+6)). I also need a
formula that will then add all hours over 6 (for example: If the hours are 8,
7, 6, 9, 6, 8 then I want the sum to be 8 (2+1+0+3+0+2)).