[µo°Ý] ­Ó¼Æ¥[Á`

shootingstar

§Ú¦bA1¨ìA7¤À§O¿é¤JA¡BB¡BC¡BD¡BE¡BF¡BG¡A¦pªG§Ú­n­pºâ°£¤FA*3+B~G³£*5ªº¥[Á`¡A¥Ø«e§Ú¥u·Q¨ì¥H¤U¤½¦¡
=if(countif(A1:A7,"A")=1,counta(A1:A7)*5-countif(A1:A7,"A")*2,counta(A1:A7)*5)=33
¤£ª¾¹D¦³¨S¦³¨ä¥L¤è¦¡¥i¹F¦¨¡H
¤S©ÎµÛ§Ú­n­pºâA~B*3+C~G*5=31¡A­n«ç»ò¼g¤½¦¡¡AÁÂÁ¡C

ML089

­ìF38¤½¦¡
=IF(COUNTIF(F$4:F$36,"A")=1,COUNTA(F$4:F$36)*5-COUNTIF(F$4:F$36,"A")*2,COUNTA(F$4:F$36)*5)

¥i¥H§ï¬°
=COUNTA(F$4:F$36)*5-COUNTIF(F$4:F$36,"A")*2

¦pªG­nÂX¼W A¡BB¡B...Hªº´î¶µ
¥i¥H§ï¬°
=COUNTA(F$4:F$36)*5-SUMPRDUCT(COUNTIF(F$4:F$36,{"A","B","C"})*{1,2,3})

©Î±N¤ñ¹ï¸ê®Æ©ñ¦bÀx¦s®æ
B44:B55 = {"A";"B";"C" ....}
C44:C55 = {1;2;3 ....}

¥i¥H§ï¬°
=COUNTA(F$4:F$36)*5-SUMPRDUCT(COUNTIF(F$4:F$36,B44:B55)*C44:C55)

Hsieh

¦^´_ 6# shootingstar

°}¦C¤½¦¡
=SUMPRODUCT(IF(ISNUMBER(MATCH(F4:F36,{"A"},0)),3,5)*(F4:F36<>""))

shootingstar

ÁÂÁÂHsieh¤Îzz5151353 ªº¦^ÂСA¦³¾Ç¨ì·s¤èªk¥i¥Hºâ¥Xµª®×¡A¤£¹L¦]¬°¨S¦³ÀɮשҥH°ÝÃD¤]¨S´y­z²M·¡¡A¦pªGA1¨ìA7¦³¤C­ÓÄæ¦ì¡A¦ý§Ú¨S¦³¥þ³¡¿é¤J¸ê®Æ¡A¥H³o¼Ëªº­ì«h¥h­pºâµª®×¡A¤@¶}©l¬O°²³]¥u¦³A¬O*3¡A¦ý¦pªG¥H«á¸ê®Æ¶V¨Ó¶V¦h¡A¥i¯à¦³H©ÎT³£¬O*3¡A³o¼Ëªº¤½¦¡¤S¸Ó¦p¦ó¤U¡AÁÂÁ¡C

zz5151353

¦^´_  zz5151353


ÁÂÁÂzz5151353 ª©¥Dªº¦^µª¡A¤½¦¡¬Ý±o¦³ÂI¦Y¤O¡A¤£¹L¦³¹F¨ì»Ý¨D¡A¥i¬O¦pªG¤µ¤Ñ§Úªº¸ê ...
shootingstar µoªí©ó 2012-12-13 17:34

¥u§i¶D§A¤@«äºû,¨Sªþ¥ó¥Î²qªº
¦pªG 3 ªº¦ì¸m¤£©w ¥B¨S¦³³W«ß,¤½¦¡´N¨S¿ìªkµÛ¤â¤F

{=SUM((A1:A7="A")*3+(A1:A7<>"A")*5)}

Hsieh

¦^´_ 3# shootingstar


    =SUM(SUMPRODUCT((A1:A7="A")*3),SUMPRODUCT((A1:A7<>"A")*5))

shootingstar

¦^´_ 2# zz5151353


ÁÂÁÂzz5151353 ª©¥Dªº¦^µª¡A¤½¦¡¬Ý±o¦³ÂI¦Y¤O¡A¤£¹L¦³¹F¨ì»Ý¨D¡A¥i¬O¦pªG¤µ¤Ñ§Úªº¸ê®Æ¬O±qA1¨ìC100¡A©Î¬O§Úªº¸ê®Æ¨S¦³·Ó¶¶§Ç±Æ¡A¨º,{3;5;5;5;5;5;5}³o´X­Ó¼Æ¦r¤£¬O­n¿é¤J300¦¸¡A¦Ó§Ú¤]¤£ª¾¹DA¬O¦b¨º¤@Äæ¦ì¡A¨º3¤]´N¤£ª¾¹D­n©ñ¦b¨º­Ó¦ì¸m¤F¡AÁÂÁ¡C

zz5151353

{=SUM(MMULT(N(A1:A7={"A","B","C","D","E","F","G"})*ROW(1:7)^0,{3;5;5;5;5;5;5}))}
OR
{=SUM(MMULT(N(A1:A7=TRANSPOSE(A1:A7))*ROW(1:7)^0,{3;5;5;5;5;5;5}))}