ExcelBanter - Simplicity is Desired

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Simplicity is Desired (https://www.excelbanter.com/excel-worksheet-functions/82399-simplicity-desired.html)

natei6

Simplicity is Desired

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+(CO UNTIF(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.

--
natei6
------------------------------------------------------------------------
natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185
View this thread: http://www.excelforum.com/showthread...hreadid=531425

Ardus Petus

Simplicity is Desired

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
--
AP

"natei6" a écrit dans
le message de ...

Hi to all,

=SUM(((COUNTIF(E20:J20,"a")+COUNTIF(E20:J20,"I"))* 10+(COUNTIF(K20:P20,"a")+C
OUNTIF(K20:P20,"I"))*20+(COUNTIF(Q20:S20,"a")+COUN TIF(Q20:S20,"I"))*30+(COUN
TIF(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.

--
natei6
------------------------------------------------------------------------
natei6's Profile:
http://www.excelforum.com/member.php...fo&userid=7185
View this thread: http://www.excelforum.com/showthread...hreadid=531425

natei6

Simplicity is Desired

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")+CO UNTIF(T5:W5,"i"))*20,(COUNTIF(E5:J5,"a")+COUNTIF(E 5: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

Ardus Petus Wrote:
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
--
AP

"natei6" a écrit
dans
le message de
...

Hi to all,

=SUM(((COUNTIF(E20:J20,"a")+COUNTIF(E20:J20,"I"))* 10+(COUNTIF(K20:P20,"a")+C
OUNTIF(K20:P20,"I"))*20+(COUNTIF(Q20:S20,"a")+COUN TIF(Q20:S20,"I"))*30+(COUN
TIF(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.

--
natei6

------------------------------------------------------------------------
natei6's Profile:
http://www.excelforum.com/member.php...fo&userid=7185
View this thread:
http://www.excelforum.com/showthread...hreadid=531425

--
natei6
------------------------------------------------------------------------
natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185
View this thread: http://www.excelforum.com/showthread...hreadid=531425

natei6

Simplicity is Desired

Biff

Simplicity is Desired

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

"natei6" wrote in
message ...

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+(CO UNTIF(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.

--
natei6
------------------------------------------------------------------------
natei6's Profile:
http://www.excelforum.com/member.php...fo&userid=7185
View this thread: http://www.excelforum.com/showthread...hreadid=531425

Harlan Grove

Simplicity is Desired

natei6 wrote...
....
=SUM(((COUNTIF(E20:J20,"a")+COUNTIF(E20:J20,"I")) *10+(COUNTIF(K20:P20,"a")
+COUNTIF(K20:P20,"I"))*20+(COUNTIF(Q20:S20,"a")+C OUNTIF(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

Simplicity is Desired

Thankyou Harlan,
That seems to work very nicely, will a similar arrangement work with
this formula? If so, how?
=IF((COUNTIF(E5:W5,"a")+COUNTIF(E5:W5,"i"))5,10+S UM(((E5:J5="a")+(E5:J5="i"))*10,((K5:P5="a")+(K5:P 5="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:S 5="i"))*30,((T5:W5="a")+(T5:W5="i"))*20))

Thanks,
Nate
Harlan Grove Wrote:
natei6 wrote...
....
=SUM(((COUNTIF(E20:J20,"a")+COUNTIF(E20:J20,"I")) *10+(COUNTIF(K20:P20,"a")
+COUNTIF(K20:P20,"I"))*20+(COUNTIF(Q20:S20,"a")+C OUNTIF(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
------------------------------------------------------------------------
natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185
View this thread: http://www.excelforum.com/showthread...hreadid=531425

Harlan Grove

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} ))

natei6

Simplicity is Desired

Harlan,
Thanks again for your help, Your a hero once again.
Nate
Harlan Grove Wrote:
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} ))

--
natei6
------------------------------------------------------------------------
natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185
View this thread: http://www.excelforum.com/showthread...hreadid=531425

All times are GMT +1. The time now is 11:53 PM.