<,> functions

[1bikenut]

Is there a formula that calculates totals in one cell and includes adding multiple cells and any number less than or equal to 8 but only to 0 and not a negative number? While another formula that contains adding multiple cells that consist of any number greater than 8. Any and all help is greatly appreciated.

 

[wigi]

Please rephrase the question, I do not understand I'm afraid.

To you want to sum cells with a number not smaller than 8?

 

[Desu Nota from Columbus]

Say you have a bunch of numbers populating your Column A. Consecutive numbers -4 to 20 in cells A1-A25.

In B1 you want the sum of only the numbers between 0 and 8 (should =28)

=SUMPRODUCT(--(A1:A25>0), --(A1:A25<8),A1:A25)

In B2, you want the sum of only the numbers greater than 8 (should equal 174):

=SUMPRODUCT(--(A1:A25>8), A1,A25)


Make sense?

 

[1bikenut]

Take information in one cell such as c6 that has 12 hrs in it and add another cell such as c25 to show any number greater than 8 hrs and to include a range of multiple cells such as c6-c23. The sum in that cell cannot equal a negative number. Example; if c6 had a sum of 6hrs the sum of c25 cannot equal -2, only 0. So if an employee had 12 hrs in cell c6 then c25 would equal 4 hrs of overtime. The cell range for c25 has to cover multiple cells (jobs) such as c6-through c25. Now from the same cell c6 that has 12 hrs in it add another cell such as q26 to show any number less than or equal to 8 hrs and to cover multiple cells (days). Example; an employee has 10 hrs monday through friday (cells c6, d6, e6, f6, g6) so cell q26 should show only 40 hrs and the other 10 hrs move into cell c26-2hrs, d27-2hrs, e28-2hrs, f29-2hrs, g30-2hrs to represent overtime for each day. Hopefully this helps, let me know if i need to explain further.