I need a cell to =5 always, unless the days are more than 5 then sum (max 7)

MegD7227 

To create a formula that ensures a cell equals 5 unless the days are more than 5 (with a maximum of 7 days), you can use the COUNTIF function along with the IF and MIN functions to handle the conditions. Here's how you can construct the formula:

1. Use the COUNTIF function to count the number of days worked.
2. Use the IF function to check if the count is greater than 5.
3. Use the MIN function to ensure the count doesn't exceed 7.

Here's the formula:

=IF(COUNTIF(G111:M111,">0") > 5, MIN(COUNTIF(G111:M111,">0"), 7), 5)

Explanation:

• COUNTIF(G111:M111,">0") counts the number of days worked (non-zero values) in the range G111:M111.
• IF(COUNTIF(G111:M111,">0") > 5, ... , 5) checks if the count of days worked is greater than 5. If true, it evaluates the MIN function; otherwise, it returns 5.
• MIN(COUNTIF(G111:M111,">0"), 7) ensures that the count does not exceed 7 days.

Example:

Let's assume the range G111:M111 contains the following values:

• If the range has values in 7 days, COUNTIF(G111:M111,">0") returns 7.
• The formula MIN(7, 7) returns 7 because it is the maximum allowed value.
• If the range has values in 4 days, COUNTIF(G111:M111,">0") returns 4.
• The formula then returns 5 because the condition COUNTIF(G111:M111,">0") > 5 is false.

Thus, this formula ensures that the cell will always be 5 unless the number of days worked is more than 5, in which case it will return the actual count with a maximum of 7. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.