Simplicity is Desired

[natei6]

Hi to all,

=SUM(((COUNTIF(E20:J20,"a")+COUNTIF(E20:J20,"I"))*10+(COUNTIF(K2020,"a")+COUNTIF(K2020,"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.

 

[Ardus Petus]

A (very little) bit shorter:
=((COUNTIF(E20:J20,"a")+COUNTIF(E20:J20,"I"))*10+(COUNTIF(K2020,"a")+COUNT
IF(K2020,"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

 

[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(K55,"a")+COUNTIF(K55,"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(K55,"a")+COUNTIF(K55,"i"))*20+(COUNTIF(Q5:S5,"a")+COUNTIF(Q5:S5,"i"))*30+(COUNTIF(T5:W5,"a")+COUNTIF(T5:W5,"i"))*20)


Thanks again,
Nate

 

[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(K55,"a")+COUNTIF(K55,"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(K55,"a")+COUNTIF(K55,"i"))*20+(COUNTIF(Q5:S5,"a")+COUNTIF(Q5:S5,"i"))*30+(COUNTIF(T5:W5,"a")+COUNTIF(T5:W5,"i"))*20)


Thanks again,
Nate

 

[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,((K2020="a")+(K2020="I"))*20,((Q20:S20="a")+(Q20:S20="I"))*30,((T20:W20="a")+(T20:W20="I"))*20)/2

Biff

 

[Harlan Grove]

natei6 wrote...
....

=SUM(((COUNTIF(E20:J20,"a")+COUNTIF(E20:J20,"I"))*10+(COUNTIF(K2020,"a")
+COUNTIF(K2020,"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.

All the ranges span only row 20, and all columns between col E and col
W are involved, and all cells are compared against the same set of
strings.

Try

=SUMPRODUCT((E20:W20={"a";"I"})
*LOOKUP(COLUMN(E20:W20),{5;11;17;20},{10;20;30;20}))/2

 

[natei6]

Thankyou Harlan,
That seems to work very nicely, will a similar arrangement work wit
this formula? If so, how?
=IF((COUNTIF(E5:W5,"a")+COUNTIF(E5:W5,"i"))>5,10+SUM(((E5:J5="a")+(E5:J5="i"))*10,((K55="a")+(K55="I"))*20,((Q5:S5="a")+(Q5:S5="I"))*30,((T5:W5="a")+(T5:W5="I"))*20),SUM(((E5:J5="a")+(E5:J5="i"))*10,((K55="a")+(K55="i"))*20,((Q5:S5="a")+(Q5:S5="i"))*30,((T5:W5="a")+(T5:W5="i"))*20))

Thanks,
Nate

 

[Harlan Grove]

natei6 wrote...
....

. . . will a similar arrangement work with
this formula? If so, how?

=IF((COUNTIF(E5:W5,"a")+COUNTIF(E5:W5,"i"))>5,10+
SUM(((E5:J5="a")+(E5:J5="i"))*10,((K55="a")+(K55="I"))*20,
((Q5:S5="a")+(Q5:S5="I"))*30,((T5:W5="a")+(T5:W5="I"))*20),
SUM(((E5:J5="a")+(E5:J5="i"))*10,((K55="a")+(K55="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,((K55="a")+(K55="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)+SUMPRODUCT((E20:W20={"a";"i"})
*LOOKUP(COLUMN(E20:W20),{5;11;17;20},{10;20;30;20}))

 

[natei6]

Harlan,
Thanks again for your help, Your a hero once again.
Nate