ExcelBanter - SumIf for Variable Conditions

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - SumIf for Variable Conditions (https://www.excelbanter.com/excel-worksheet-functions/118481-sumif-variable-conditions.html)

SumIf for Variable Conditions

I have a brain teaser funtion needed for a time sheet.
Here is the set-up:
Example Numbers:
AE AJ AL
Actual Hours Total Hours Comp Time
171 180 = 12

This is how comp time should be calculated.
Here are the conditions:
Comp time is given for the first three Actual Hours over 168. Up until 171
hours.
For example, if actual hours is 171 and total hours is 180 than comp time
should equal 12. Here is the current function I am using. However, when
these numbers are pulgged in comp time stays at 3:
=IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168)))
How can I tweek this to work correctly?

Many thanks

JP

Chris Former Excel Support Professional

SumIf for Variable Conditions

"JP" wrote:

I have a brain teaser funtion needed for a time sheet.
Here is the set-up:
Example Numbers:
AE AJ AL
Actual Hours Total Hours Comp Time
171 180 = 12

This is how comp time should be calculated.
Here are the conditions:
Comp time is given for the first three Actual Hours over 168. Up until 171
hours.
For example, if actual hours is 171 and total hours is 180 than comp time
should equal 12. Here is the current function I am using. However, when
these numbers are pulgged in comp time stays at 3:
=IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168)))
How can I tweek this to work correctly?

Many thanks

JP

Try this =IF(AJ6=180,12,IF(AE6=171,3,0)) the only thing is that you did
not say what you wanted to do if there was no comp time so had it return a
zero.

I hope this helps

SumIf for Variable Conditions

The numbers may fluctuate in each column. These are not going to be the set
numbers becasue times may differ. Also,if no comp time then zero.
Thanks for the response
JP

"Chris Former Excel Support Professional" wrote:

"JP" wrote:

I have a brain teaser funtion needed for a time sheet.
Here is the set-up:
Example Numbers:
AE AJ AL
Actual Hours Total Hours Comp Time
171 180 = 12

This is how comp time should be calculated.
Here are the conditions:
Comp time is given for the first three Actual Hours over 168. Up until 171
hours.
For example, if actual hours is 171 and total hours is 180 than comp time
should equal 12. Here is the current function I am using. However, when
these numbers are pulgged in comp time stays at 3:
=IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168)))
How can I tweek this to work correctly?

Many thanks

JP

Try this =IF(AJ6=180,12,IF(AE6=171,3,0)) the only thing is that you did
not say what you wanted to do if there was no comp time so had it return a
zero.

I hope this helps

Bob Phillips

SumIf for Variable Conditions

I don't get why it is 12 in your example, but perhaps

=IF(AE6=171,AJ6-168,AE6-168)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JP" wrote in message
...
The numbers may fluctuate in each column. These are not going to be the
set
numbers becasue times may differ. Also,if no comp time then zero.
Thanks for the response
JP

"Chris Former Excel Support Professional" wrote:

"JP" wrote:

I have a brain teaser funtion needed for a time sheet.
Here is the set-up:
Example Numbers:
AE AJ AL
Actual Hours Total Hours Comp Time
171 180 = 12

This is how comp time should be calculated.
Here are the conditions:
Comp time is given for the first three Actual Hours over 168. Up
until 171
hours.
For example, if actual hours is 171 and total hours is 180 than comp
time
should equal 12. Here is the current function I am using. However,
when
these numbers are pulgged in comp time stays at 3:
=IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168)))
How can I tweek this to work correctly?

Many thanks

JP

Try this =IF(AJ6=180,12,IF(AE6=171,3,0)) the only thing is that you
did
not say what you wanted to do if there was no comp time so had it return
a
zero.

I hope this helps

Roger Govier

SumIf for Variable Conditions

Hi

Maybe
=IF(AE1<168,0,AJ1-AE1+MIN(3,AJ1-168))

--
Regards

Roger Govier

"JP" wrote in message
...
The numbers may fluctuate in each column. These are not going to be
the set
numbers becasue times may differ. Also,if no comp time then zero.
Thanks for the response
JP

"Chris Former Excel Support Professional" wrote:

"JP" wrote:

I have a brain teaser funtion needed for a time sheet.
Here is the set-up:
Example Numbers:
AE AJ AL
Actual Hours Total Hours Comp Time
171 180 = 12

This is how comp time should be calculated.
Here are the conditions:
Comp time is given for the first three Actual Hours over 168. Up
until 171
hours.
For example, if actual hours is 171 and total hours is 180 than
comp time
should equal 12. Here is the current function I am using.
However, when
these numbers are pulgged in comp time stays at 3:
=IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168)))
How can I tweek this to work correctly?

Many thanks

JP

Try this =IF(AJ6=180,12,IF(AE6=171,3,0)) the only thing is that you
did
not say what you wanted to do if there was no comp time so had it
return a
zero.

I hope this helps

SumIf for Variable Conditions

This is the exact answer to the problem

Thanks a bunch

JP

"Roger Govier" wrote:

Hi

Maybe
=IF(AE1<168,0,AJ1-AE1+MIN(3,AJ1-168))

--
Regards

Roger Govier

"JP" wrote in message
...
The numbers may fluctuate in each column. These are not going to be
the set
numbers becasue times may differ. Also,if no comp time then zero.
Thanks for the response
JP

"Chris Former Excel Support Professional" wrote:

"JP" wrote:

I have a brain teaser funtion needed for a time sheet.
Here is the set-up:
Example Numbers:
AE AJ AL
Actual Hours Total Hours Comp Time
171 180 = 12

This is how comp time should be calculated.
Here are the conditions:
Comp time is given for the first three Actual Hours over 168. Up
until 171
hours.
For example, if actual hours is 171 and total hours is 180 than
comp time
should equal 12. Here is the current function I am using.
However, when
these numbers are pulgged in comp time stays at 3:
=IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168)))
How can I tweek this to work correctly?

Many thanks

JP

Try this =IF(AJ6=180,12,IF(AE6=171,3,0)) the only thing is that you
did
not say what you wanted to do if there was no comp time so had it
return a
zero.

I hope this helps

Roger Govier

SumIf for Variable Conditions

Hi

You're very welcome. Thanks for the feedback.

--
Regards

Roger Govier

"JP" wrote in message
...
This is the exact answer to the problem

Thanks a bunch

JP

"Roger Govier" wrote:

Hi

Maybe
=IF(AE1<168,0,AJ1-AE1+MIN(3,AJ1-168))

--
Regards

Roger Govier

"JP" wrote in message
...
The numbers may fluctuate in each column. These are not going to be
the set
numbers becasue times may differ. Also,if no comp time then zero.
Thanks for the response
JP

"Chris Former Excel Support Professional" wrote:

"JP" wrote:

I have a brain teaser funtion needed for a time sheet.
Here is the set-up:
Example Numbers:
AE AJ AL
Actual Hours Total Hours Comp Time
171 180 = 12

This is how comp time should be calculated.
Here are the conditions:
Comp time is given for the first three Actual Hours over 168.
Up
until 171
hours.
For example, if actual hours is 171 and total hours is 180 than
comp time
should equal 12. Here is the current function I am using.
However, when
these numbers are pulgged in comp time stays at 3:
=IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168)))
How can I tweek this to work correctly?

Many thanks

JP

Try this =IF(AJ6=180,12,IF(AE6=171,3,0)) the only thing is that
you
did
not say what you wanted to do if there was no comp time so had it
return a
zero.

I hope this helps

All times are GMT +1. The time now is 07:02 PM.