Sumif or if statements-Help!
 

I have a spreadsheet listing employee hours per week. I want to create a
formula that will total up the total regular hours and the overtime hours
seperately. I would like the spreadsheet to total the 118 regular hours and
then the 2 overtime hours in the cell underneath. I hope this makes sense.
Thanks in advance for you assistance.

Sarah
40
38
42

Total Regular Hours 118
Total Overtime Hours 2

One way would be to use the following ARRAY Formulas. They must be ntered
with Ctrl+Shift+Enter:

To calculate non-OT:
=SUM(IF(A1:A340,40,A1:A3))

To calculate OT:
=SUM(IF(A1:A340,A1:A3-40))

tj

"ber_harris" wrote:

I have a spreadsheet listing employee hours per week. I want to create a
formula that will total up the total regular hours and the overtime hours
seperately. I would like the spreadsheet to total the 118 regular hours and
then the 2 overtime hours in the cell underneath. I hope this makes sense.
Thanks in advance for you assistance.

Sarah
40
38
42

Total Regular Hours 118
Total Overtime Hours 2

One way, to get OT hours

=SUMPRODUCT((A2:A640)*(A2:A6-40))

to get regular hours

=SUM(A2:A6)-SUMPRODUCT((A2:A640)*(A2:A6-40))

you should replace SUMPRODUCT((A2:A640)*(A2:A6-40))
in the second formula with the cell holding the OT formula

so if the OT formula is in B3, in B2 use

=SUM(A2:A6)-B3

Regards,

Peo Sjoblom


"ber_harris" wrote:

I have a spreadsheet listing employee hours per week. I want to create a
formula that will total up the total regular hours and the overtime hours
seperately. I would like the spreadsheet to total the 118 regular hours and
then the 2 overtime hours in the cell underneath. I hope this makes sense.
Thanks in advance for you assistance.

Sarah
40
38
42

Total Regular Hours 118
Total Overtime Hours 2

Thank you. Using these formulas, I received #VALUE! in both cells. I know
there has to be a way to get it to add all of regular work hours in one cell
and the overtime in the other...I just don't know what it is.


"tjtjjtjt" wrote:

One way would be to use the following ARRAY Formulas. They must be ntered
with Ctrl+Shift+Enter:

To calculate non-OT:
=SUM(IF(A1:A340,40,A1:A3))

To calculate OT:
=SUM(IF(A1:A340,A1:A3-40))

tj

"ber_harris" wrote:

I have a spreadsheet listing employee hours per week. I want to create a
formula that will total up the total regular hours and the overtime hours
seperately. I would like the spreadsheet to total the 118 regular hours and
then the 2 overtime hours in the cell underneath. I hope this makes sense.
Thanks in advance for you assistance.

Sarah
40
38
42

Total Regular Hours 118
Total Overtime Hours 2

Do you have the formula typed exactly? Did you update the cell references to
match your actual cell references? Are there any cells in the range that are
not actual numbers? Did you enter the formula with Ctrl+Shift+Enter?
Not that it helps you, but I've tested it on my computer, and it's working.

tj

"ber_harris" wrote:

Thank you. Using these formulas, I received #VALUE! in both cells. I know
there has to be a way to get it to add all of regular work hours in one cell
and the overtime in the other...I just don't know what it is.


"tjtjjtjt" wrote:

One way would be to use the following ARRAY Formulas. They must be ntered
with Ctrl+Shift+Enter:

To calculate non-OT:
=SUM(IF(A1:A340,40,A1:A3))

To calculate OT:
=SUM(IF(A1:A340,A1:A3-40))

tj

"ber_harris" wrote:

I have a spreadsheet listing employee hours per week. I want to create a
formula that will total up the total regular hours and the overtime hours
seperately. I would like the spreadsheet to total the 118 regular hours and
then the 2 overtime hours in the cell underneath. I hope this makes sense.
Thanks in advance for you assistance.

Sarah
40
38
42

Total Regular Hours 118
Total Overtime Hours 2

I can create a #VALUE error in my formula if one of the cells is actually
Text and not a number. Do you have any text in the range you are trying to
add?
Is it possible that some of the numbers are formatted as text?
That will have to change to make the formula work, as it is currently written.
Does Peo Sjoblom's formula work on your sheet? I get an error with that one
as well if text is in the range. If not, it calculates correctly.

tj


"ber_harris" wrote:

Thank you. Using these formulas, I received #VALUE! in both cells. I know
there has to be a way to get it to add all of regular work hours in one cell
and the overtime in the other...I just don't know what it is.


"tjtjjtjt" wrote:

One way would be to use the following ARRAY Formulas. They must be ntered
with Ctrl+Shift+Enter:

To calculate non-OT:
=SUM(IF(A1:A340,40,A1:A3))

To calculate OT:
=SUM(IF(A1:A340,A1:A3-40))

tj

"ber_harris" wrote:

I have a spreadsheet listing employee hours per week. I want to create a
formula that will total up the total regular hours and the overtime hours
seperately. I would like the spreadsheet to total the 118 regular hours and
then the 2 overtime hours in the cell underneath. I hope this makes sense.
Thanks in advance for you assistance.

Sarah
40
38
42

Total Regular Hours 118
Total Overtime Hours 2