I am trying to solve a problem here is the criteria: I have a number of cells (9 in total) that require a score of 1-3. They will effect the total score for cell AB21.

I have a count function counting all the number 1's in cell AB40
and a count function counting all the number 2's in AC40

These are the only two numeric values that affect the outcome. And matter.
Cell AB21 will have a possible value of 1-4
The criteria are as follows a rating(value) of 4 = no more than 1 (number 1) in cell ab40. And no (number 2's) in ac40

a rating of 3 = 1 (number 1) and 0-1 (number 2's) or 0 (number 1's) and 2-3 (number 2's)

a rating of 2 = 2 (number 1's) and 0-1 (number 2's) or 1 (number 1) and 2-3 (number 2's) or 0 (number 1's) and 4+ (number 2's)

a rating of 1 = 3+ (number 1's) or 6+ (number 2's) or 2 (number 1's) and 2+ (number 2's) or 1 (number 1) and 4+ (number 2's)

The formula I am attemting to use is as follows:
=IF(AND(AB40<1,AC40<=1),4,IF(AND(AB40=1,AC40<=1),3 ,IF(AND(AB40=0,AC40>1<=3),3,if(and(AB40=2,ac40<=1) ,2,if(and(ab40=1,ac40>=2<=3),2,if(and(ab40=0,ac40= 4),2,if(ab40>3,1,if(ac40>6,1,if(and(ab40=2,ac40>=2 ),1,if(and(ab40=1,ac40>=4,1)))))))))

I am probably making this more difficult than it has to be, and you probably have no idea what I am trying to do after reading this. But if you have any ideas I would love to hear them.

Thanks for the help

I'm afraid I just do not understand!
Perhaps you can provide us with sample data and expected output to help illustrate your problem

historically Ive always found it better to do this sort of thing in small steps
so I'd assign the rating 1,2 or 3 to an individual section
if you need to do further processing then do that in cells for each row
then combine those ratings in your final cell

one of the problems I find with spreadsheets is that its very easy to make a change in one place, expecting the app to pick up thoise chanegs, but its very easy to make chanegs, destroy the link behind the scenes and not notice your app is now broken.

Wolgie

If I have understood your question, this is the basis of the formula you require

But due to the limitation of excel only allowing 7 If staments I had to split out the rating

Cell A25 =IF(AND(OR(B14=2,B14=3),A14=0),3,IF(AND(OR(B14=0,B 14=1),A14=1),3,IF(AND(A14=0,B14>=4),2,IF(AND(OR(B1 4=2,B14=3),A14=1),2,IF(AND(OR(B14=0,B14=1),A14=2), 2,0)))))

Cell B25 =IF(AND(A14=2,B14>=2),1,IF(AND(A14=1,B14>=4),1,IF( AND(A14>=3,B14>=6),1,0)))

Merge the two above

Cell A29 =IF(AND(A25>=2,B25=0),A25,IF(AND(B25=1,A25<=0),B25 ,""))


See attached file, in my example Cell ref A29 is the Resulting Rating

Hope this helps