Help! Count value, if there is values in more than 6 cells

[Lmao]

Ok so I have a formula which calculates overtime. It pulls data from a range of 7 cells. Numbers are inputted into these cells and any number over 8 ($BC$12) is counted as overtime. However what I now want to add is a condition that if there is data in more than six of the below referenced cells, then the value in the 7th cell automatically gets added to the total regardless of its value. So as an example in the formula below if for example I have data in the cells as follows:

T17 = 4
X17 = 4
AB17 = 4
AF17 = 4
AJ17 = 4
AN17 = 4
AR17 = 4

The total number of hours is 28 so it would not trigger a secondary formula for overtime counted over 40 hours, and since none of the cells values exceed 8 then that would not trigger this formula to count it as overtime. What i would like help in doing is since there IS values in more than six of the cells the value in AR17 should automatically counted so in this instance 4 would be the correct answer.

Would nesting the below formula into a counta do the trick and if so how would I go about doing that and setting the condition to include the any number if the there is values greater than 0 in greater than 6 cells?

Please Login or Register  to view this content.

[davsth]

It is not clear if you are paying overtime as say AR17=10 in the above scenario, would you wish to add 10 to the result? would this lead to double counting adding +and(t17>0,x17>0,AB17>0.Af17>0, Aj17>0,AN17>0)*AR17 to your formula would do what you ask, but it might not be what you want

Dav

[DannyJ]

I believe the below is what you are looking for?

=IF(COUNT(A17:F17)=6,MIN(4,MAX(0,(G17-$A$12)+((MIN(4,MAX(0,(A17-$A$12)+MIN(4,MAX(0,(B17-$A$12)))+MIN(4,MAX(0,(C17-$A$12)))+MIN(4,MAX(0,(D17-$A$12)))+MIN(4,MAX(0,(E17-$A$12)))+MIN(4,MAX((F17-$A$12))))))))),MIN(4,MAX(0,(A17-$A$12)))+MIN(4,MAX(0,(B17-$A$12)))+MIN(4,MAX(0,(C17-$A$12)))+MIN(4,MAX(0,(D17-$A$12)))+MIN(4,MAX(0,(E17-$A$12)))+MIN(4,MAX((F17-$A$12)))+MIN(4,MAX(0,(G17-$A$12))))

[Lmao]

I believe the below is what you are looking for?

=IF(COUNT(A17:F17)=6,MIN(4,MAX(0,(G17-$A$12)+((MIN(4,MAX(0,(A17-$A$12)+MIN(4,MAX(0,(B17-$A$12)))+MIN(4,MAX(0,(C17-$A$12)))+MIN(4,MAX(0,(D17-$A$12)))+MIN(4,MAX(0,(E17-$A$12)))+MIN(4,MAX((F17-$A$12))))))))),MIN(4,MAX(0,(A17-$A$12)))+MIN(4,MAX(0,(B17-$A$12)))+MIN(4,MAX(0,(C17-$A$12)))+MIN(4,MAX(0,(D17-$A$12)))+MIN(4,MAX(0,(E17-$A$12)))+MIN(4,MAX((F17-$A$12)))+MIN(4,MAX(0,(G17-$A$12))))

Couldn't get this to work, I tried replacing the cells with the ones I had in my formula above it looks like this now

Please Login or Register  to view this content.

It gave me a result of -4 when all 4s were inputted. However when I changed the values of the cells to 8's the result was "0". The correct result should have been 8 since there is data in all seven of the cells. The seventh cell, AR17, should be counted to give me a total of 8.

T17 = 8
X17 = 8
AB17 = 8
AF17 = 8
AJ17 = 8
AN17 = 8
AR17 = 8

It is not clear if you are paying overtime as say AR17=10 in the above scenario, would you wish to add 10 to the result? would this lead to double counting adding +and(t17>0,x17>0,AB17>0.Af17>0, Aj17>0,AN17>0)*AR17 to your formula would do what you ask, but it might not be what you want

Dav

I got an error when I tried to input this formula. When I inputted the formula it looks like this

Please Login or Register  to view this content.

[JohnTopley]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[DannyJ]

Lmao, If any other cells between T17 and AR17 then it is always going to count above 6 and not 6 exactly. Without a spreadsheet as John has mentioned I am not sure exactly how the spreadsheet is working.

[JohnTopley]

You will only get more than 6 if ALL 7 are filled so confused by addition of AR17 logic.

[Lmao]

I don't have the spreadsheet available at this exact moment however I figured out the formula on my own. I just added =(IF((COUNTIF(T17:AR17,">0"))>6,+AR17,"0"))+ to the front of the formula and it works pretty well for what I need it to do.

[JohnTopley]

Remove quotes as this treated as text not number

=(COUNTIF(T17:AR17,">0"))>6,+AR15,0))

And why are you adding AR15 (not AR17) ? .... which was not specified in your original post