Add hours 'up-to' a certain value?

I am trying to add employees' hours of supervisory conferences, up to (20). This total is used in another formula on another sheet, as well as a PivoTable. Most people have less than 20 hours, however some people have more. We count 20 hours of conferences, but no more.
I have looked everywhere. Please help!
So far I have tried SUMIF, SUMIFS, SUMIF(OR, and a boat load of others...

Since the last question was answered perfectly...I need to find out if I can combine the =MIN(SUM(B:B),20) with another formula that matches the totals with a 'code' from another column (sort of a unique identifier).

I am sorry to be a bother.

The columns are laid out that the UI is in column D and also column Y (column Y is linked from another worksheet, and is necessary to add all hours from the workbook.) For some reason, this persons UI's are not lining up, and it is giving me incorrect information.

Hi,

Have you tried wrapping your current formulae in a MAX() function?

e.g.

MAX(20,yourformula)

Regards

Well, you don't give much to work on ;-)

This will add up values in column B to a maximum of 20

=MIN(SUM(B:B),20)

Regards

@Richard: not my understanding of what the OP wanted ;-)

Regards

You're welcome. Thanks for the rep.

If this has answered your question, please mark your thread as solved. See my signature for details or the FAQ.

Regards

....and isn't it so often the the case

I am trying to add employees' hours of supervisory conferences, up to (20). This total is used in another formula on another sheet, as well as a PivoTable. Most people have less than 20 hours, however some people have more. We count 20 hours of conferences, but no more.
I have looked everywhere. Please help!
So far I have tried SUMIF, SUMIFS, SUMIF(OR, and a boat load of others...

Since the last question was answered perfectly...I need to find out if I can combine the =MIN(SUM(B:B),20) with another formula that matches the totals with a 'code' from another column (sort of a unique identifier).

I am sorry to be a bother.

The columns are laid out that the UI is in column D and also column Y (column Y is linked from another worksheet, and is necessary to add all hours from the workbook.) For some reason, this persons UI's are not lining up, and it is giving me incorrect information.

Again, you don't provide much to go on. Last time I offered a generic example which you presumably were able to adapt to your requirements.

This time your question is more specific and I'm not going to try and second guess what you want. If I do, it's likely to bounce backwards and forwards for ever.

Please read the forum rules and guidelines ... one of which is, please post a sample workbook if you want a sensible considered response.

Without being able to see what you're working with, it can be a waste of everyone's time and quite frustrating.

Regards

OK, we've got something to look at, thanks. However, *now* I am really confused ;-)

I guess we're looking at the Sups Conf tab? In column V, you have the total of the hours for the row ... for the employee. And, in column X you have the "limiter" formula; seven (7) people have hours in excess of 20 and are correctly evaluated to 20. As a matter of interest, why have you put the 20 in quotes? Fot example, =MIN(SUM(V26),"20") could, and should, be :=MIN(SUM(V26),20)

And then, in column Y, you have another employee number ... but not the same as in column D. And, apparently, in no particular order.

So, the crux of the matter is ... what do you want to do?

Regards

Originally Posted by TMShucks

OK, we've got something to look at, thanks. However, *now* I am really confused ;-)

I guess we're looking at the Sups Conf tab? In column V, you have the total of the hours for the row ... for the employee. And, in column X you have the "limiter" formula; seven (7) people have hours in excess of 20 and are correctly evaluated to 20. As a matter of interest, why have you put the 20 in quotes? Fot example, =MIN(SUM(V26),"20") could, and should, be :=MIN(SUM(V26),20)

And then, in column Y, you have another employee number ... but not the same as in column D. And, apparently, in no particular order.

So, the crux of the matter is ... what do you want to do?

Regards

On the MAT worksheet, in column AC I am totaling all trainings from each worksheet with links from the last two columns, by matching the EMP #. On the SupsConf sheet the EMP # column on the far right is linked from the first worksheet (MAT) and is used to provide a Grand Total from the worksheet. I do not understand how this person's wksht managed to get out of alignment, since it should include ALL employees and match the MAT sheet. This is why I need the SubTotal column (X) on the Sups Conf sheet to match the EMP # (Y) to the right.

On my other pages, I either used SUMPRODUCT or SUMIFS, but since I need a total that provides only hours up to 20 hours, I am not sure if I can combine the two. If I cannot, is there a work around? Like linking the totals to another cell and then SUMPROCT/SUMIF?

Bottom line...I need to know each employee's hours of SUP CONF by employee number, and I need to be able to reference that on my MAT page.

Sorry this is so confusing!

I'm probably just being thick but I stll don't understand the relevance of column Y.

Not to worry, without looking at specifics, if you have a SUMPRODUCT that gives you the answer that it should, barring being limited to a maximum of 20 hours, you should be able to say:

=MIN(SUMPRODUCT((....whatever...)*(...)*(...)...),20)


Regards