I'm creating a dashboard in excel and I'm trying to put together metrics.

I'm giving 1 for each yes, 0 for a no and -1 if Column G is a no. Giving a maximum potential score of 6.

The requirement I'm struggling with is this:

1. If column D is no the score cannot exceed 3
2. If column E is no the score cannot exceed 4

My total is in colum H and I've tried to use this as a limiter but all that does is set the cell value to either 3 or whatever the value in column H is: =IF(D3="No",MAX(3),H1)

Is there a way to apply a limiter like this into excel or am I asking too much?

In the above examples this is what I want the answers to be: x=4, y=4, z=3

CodePudding user response：

Use:

=MIN(IF(D1="No",3,7),IF(E1="No",4,7),COUNTIF(B1:G1,"Yes")-(G1="No"))