| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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} )) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help! Formula that can Ref. hidden data & Populate Cells with desired text | Excel Worksheet Functions | |||
| Delete all data (formulas) below a desired row. | Excel Discussion (Misc queries) | |||
| Linking across worksheets not working as desired | Excel Worksheet Functions | |||
| highlight range if cell contains desired data | New Users to Excel | |||
| hiding all data in a sheet except desired | Excel Worksheet Functions | |||
Linear Mode
