Simplicity is Desired
natei6 wrote...
....
. . . will a similar arrangement work with
this formula? If so, how?
=IF((COUNTIF(E5:W5,"a")+COUNTIF(E5:W5,"i"))5,1 0+
SUM(((E5:J5="a")+(E5:J5="i"))*10,((K5:P5="a")+(K5 :P5="I"))*20,
((Q5:S5="a")+(Q5:S5="I"))*30,((T5:W5="a")+(T5:W5= "I"))*20),
SUM(((E5:J5="a")+(E5:J5="i"))*10,((K5:P5="a")+(K5 :P5="i"))*20,
((Q5:S5="a")+(Q5:S5="i"))*30,((T5:W5="a")+(T5:W5= "i"))*20))
....
First, (COUNTIF(E5:W5,"a")+COUNTIF(E5:W5,"i"))5 could be replaced with
SUM(COUNTIF(E5:W5,{"a","i"}))5
The only difference between the TRUE and FALSE results is the 10+ in
the TRUE result. Which means your formula is equivalent to
=IF(SUM(COUNTIF(E5:W5,{"a","i"}))5,10,0)
+SUM(((E5:J5="a")+(E5:J5="i"))*10,((K5:P5="a")+(K5 :P5="I"))*20,
((Q5:S5="a")+(Q5:S5="I"))*30,((T5:W5="a")+(T5:W5=" I"))*20)
Then replace the first two lines with my formula, so
=IF(SUM(COUNTIF(E5:W5,{"a","i"}))5,10,0)+SUMPRODU CT((E20:W20={"a";"i"})
*LOOKUP(COLUMN(E20:W20),{5;11;17;20},{10;20;30;20} ))
|