Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Sum Values Based On Text

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Sum Values Based On Text

    Hi,

    I need a formula that will calculate the sum total of 6 and a longer formula to cope with 12 letters in a range. The third value in both ranges is worth double points.

    The letters and their corresponding values are:

    F=0, P=2, M=4, D=6

    Some 6 letter examples to illustrate:

    in A3:D3 the letters D,D,D,D,D,D return the value of 42 in E3 (6+6+12 (third letter is worth double)+6+6+6
    in A4:D4 the letters P, P, F, F, M, M return the value of 12 in E4

    Some 12 letter examples to illustrate:

    in A3:L3 the letters D,D,D,D,D,D,D,D,D,D,D,D return the value of 78 (6+6+12+6+6+6+6+6+6+6+6+6)

    If the range is empty the formula should leave the cell blank.

    Thanks again for any help
    Last edited by Upside; November 8th, 2006, 21:30.
    I am new to VBA - comments on how to improve my code are always welcome.

  • #2
    Re: Sum Values Based On Text

    This is the answer to the original question:

    =IF(COUNTIF(A3:D3,"="""),"",SUM(COUNTIF(A3:D3,"=F")*0,COUNTIF(A3:D3,"=P")*2,COUNTIF(A3:D3,"=MF")*4,COUNTIF(A3:D3,"=D")*6))

    Sorry, but your edit makes it impossible to do it this way. I am not going to waste any more time on this.
    .

    Comment


    • #3
      Re: Sum Values Based On Text

      I am sorry BytheCringe for inadvertently messing you around. My supervisor asked me to do a 6 letter analysis initially then came back 15 minutes later to say that a 12 letter one was needed too. I did my utmost to edit it quickly and thought I had succeeded. My sincerest apologies to you - I am assured by my supervisor that there are no further details I should know.

      If anyone else can contribute (like Krishna or Domenic) that would be great.
      I am new to VBA - comments on how to improve my code are always welcome.

      Comment


      • #4
        Re: Sum Values Based On Text

        Originally posted by Upside
        I am sorry BytheCringe for inadvertently messing you around. My supervisor asked me to do a 6 letter analysis initially then came back 15 minutes later to say that a 12 letter one was needed too. I did my utmost to edit it quickly and thought I had succeeded. My sincerest apologies to you - I am assured by my supervisor that there are no further details I should know.

        If anyone else can contribute (like Krishna or Domenic) that would be great.
        Upside, Your original post was for four letters (your post still mentions A3:D3). Still, I understand your problem with a supervisor, and your handsome apology is accepted with thanks. I'll post you the 6- and 12-letter versions shortly.
        .

        Comment


        • #5
          Re: Sum Values Based On Text

          Upside

          Try the attached for the 6 letter part, the 12 letter part can be made from the 6 quite easily.

          regards

          Ian
          Attached Files

          Comment


          • #6
            Re: Sum Values Based On Text

            Try this array formula
            note: it should all be in the one cell and commited using CTRL+SHIFT+ENTER
            =IF(SUM(--ISNUMBER(FIND("P",A3:L3)))>2,(1+SUM(--ISNUMBER(FIND("P",A3:L3))))*2,SUM(--ISNUMBER(FIND("P",A3:L3)))*2)+ 
            IF(SUM(--ISNUMBER(FIND("D",A3:L3)))>2,(1+SUM(--ISNUMBER(FIND("D",A3:L3))))*6,SUM(--ISNUMBER(FIND("D",A3:L3)))*6)+
            IF(SUM(--ISNUMBER(FIND("M",A3:L3)))>2,(1+SUM(--ISNUMBER(FIND("M",A3:L3))))*4,SUM(--ISNUMBER(FIND("M",A3:L3)))*4)



            And regarding the edit. It may have been better to post the amended request in this thread rather than edit the original post.

            Cheers
            Andy

            Comment


            • #7
              Re: Sum Values Based On Text

              Here are the 6- and 12-letter versions:

              =IF(COUNTIF(A3:F3,"="""),"",SUM(COUNTIF(A3:F3,"=F")*0,COUNTIF(A3:F3,"=P")*2,COUNTIF(A3:F3,"=M")*4,COUNTIF(A3:F3,"=D")*6,(C3="P")*2,(C3="M")*4,(C3="D")*6))

              =IF(COUNTIF(A3:L3,"="""),"",SUM(COUNTIF(A3:L3,"=F")*0,COUNTIF(A3:L3,"=P")*2,COUNTIF(A3:L3,"=M")*4,COUNTIF(A3:L3,"=D")*6,(C3="P")*2,(C3="M")*4,(C3="D")*6))

              EDITED: "MF" changed to correct "M"

              EDIT: The SUM(COUNTIF) for "F" is not needed, Doh!
              Last edited by ByTheCringe2; November 8th, 2006, 22:45.
              .

              Comment


              • #8
                Re: Sum Values Based On Text

                Thank you BytheCringe for taking the time to recode this - it is much appeciated.

                Yes, on hindsight it would have been better to do another post rather than edit it - I am sorry for any inconvenience caused.
                I am new to VBA - comments on how to improve my code are always welcome.

                Comment


                • #9
                  Re: Sum Values Based On Text

                  Hi,

                  =IF(COUNTA(A3:INDEX(A3:L3,,A1))=MAX(3,A1),SUM(COUNTIF(A3:INDEX(A3:L3,A1),{"f","p","m","d"})*{0,2,4,6})+LOOKUP(INDEX(A3:L3,,3),{"d","f","m","p"},{6,0,4,2}),"")

                  This is a dynamic range formula. Just put 6, 12 or whatever in A1.

                  HTH
                  Last edited by Krishnakumar; November 9th, 2006, 17:58.
                  Kris

                  ExcelFox

                  Comment


                  • #10
                    Re: Sum Values Based On Text

                    Just a note of caution on my formula as I appear to have read the request differently to the others.

                    I double count the 3rd occurance of any character not just the one in the 3rd position.

                    So for the following both would double count the 3rd P
                    in A4:F4 the letters P, P, P, F, M, M return the value of 14 in E4

                    in A4:F4 the letters P, P, F, P, M, M return the value of 14 in E4

                    Cheers
                    Andy

                    Comment

                    Trending

                    Collapse

                    There are no results that meet this criteria.

                    Working...
                    X