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

[MegD7227]

I am calculating Per Diem Days.  Right now I use =COUNTIF(G111:M111,">0") for all employees, there is one employee who is paid 5 no matter days worked unless he more than 5 with a max of 7 days in the week.  I am not sure what formula I should use.  

[#2]

@MegD7227 

It's better if you share sample file which illustrates how your data is structured. In particular, how Excel knows how many days employee worked in this or that week.

[#3 · Accepted solution]

@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:

G111	H111	I111	J111	K111	L111	M111
1	1	1	1	1	1	1

• 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.

[#4]

This was PERFECT! Exactly what I needed! Thank you so much! I appreciate the time you took to show and explain it. :)