Simplicity is Desired

N

natei6

Hi to all,

=SUM(((COUNTIF(E20:J20,"a")+COUNTIF(E20:J20,"I"))*10+(COUNTIF(K20:p20,"a")+COUNTIF(K20:p20,"I"))*20+(COUNTIF(Q20:S20,"a")+COUNTIF(Q20:S20,"I"))*30+(COUNTIF(T20:W20,"a")+COUNTIF(T20:W20,"I"))*20))/2

This formula that I have put together gets the desired result, but I am
seeking a simpler version. Many thanks.
Nate.
 
A

Ardus Petus

A (very little) bit shorter:
=((COUNTIF(E20:J20,"a")+COUNTIF(E20:J20,"I"))*10+(COUNTIF(K20:p20,"a")+COUNT
IF(K20:p20,"I"))*20+(COUNTIF(Q20:S20,"a")+COUNTIF(Q20:S20,"I"))*30+(COUNTIF(
T20:W20,"a")+COUNTIF(T20:W20,"I"))*20)/2

since the SUM function is unnecessary

HTH
 
N

natei6

Thanks, very much.
I am curious, would a sumproduct function achieve the same result? I
also have another fomula that is similar, only with an if function
added.

=IF((COUNTIF(E5:W5,"a")+COUNTIF(E5:W5,"i")>5),10+(COUNTIF(E5:J5,"a")+COUNTIF(E5:J5,"i"))*10+(COUNTIF(K5:p5,"a")+COUNTIF(K5:p5,"i"))*20+(COUNTIF(Q5:S5,"a")+COUNTIF(Q5:S5,"I"))*30+(COUNTIF(T5:W5,"a")+COUNTIF(T5:W5,"i"))*20,(COUNTIF(E5:J5,"a")+COUNTIF(E5:J5,"i"))*10+(COUNTIF(K5:p5,"a")+COUNTIF(K5:p5,"i"))*20+(COUNTIF(Q5:S5,"a")+COUNTIF(Q5:S5,"i"))*30+(COUNTIF(T5:W5,"a")+COUNTIF(T5:W5,"i"))*20)


Thanks again,
Nate
 
N

natei6

Thanks, very much.
I am curious, would a sumproduct function achieve the same result? I
also have another fomula that is similar, only with an if function
added.

=IF((COUNTIF(E5:W5,"a")+COUNTIF(E5:W5,"i")>5),10+(COUNTIF(E5:J5,"a")+COUNTIF(E5:J5,"i"))*10+(COUNTIF(K5:p5,"a")+COUNTIF(K5:p5,"i"))*20+(COUNTIF(Q5:S5,"a")+COUNTIF(Q5:S5,"I"))*30+(COUNTIF(T5:W5,"a")+COUNTIF(T5:W5,"i"))*20,(COUNTIF(E5:J5,"a")+COUNTIF(E5:J5,"i"))*10+(COUNTIF(K5:p5,"a")+COUNTIF(K5:p5,"i"))*20+(COUNTIF(Q5:S5,"a")+COUNTIF(Q5:S5,"i"))*30+(COUNTIF(T5:W5,"a")+COUNTIF(T5:W5,"i"))*20)


Thanks again,
Nate
 
B

Biff

Hi!

A little bit shorter.....

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=SUM(((E20:J20="a")+(E20:J20="I"))*10,((K20:p20="a")+(K20:p20="I"))*20,((Q20:S20="a")+(Q20:S20="I"))*30,((T20:W20="a")+(T20:W20="I"))*20)/2

Biff
 
Top