Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Sum Values Based On Text

  1. #1
    Join Date
    2nd October 2006
    Posts
    326

    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 at 21:30.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    2nd October 2006
    Posts
    326

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Sum Values Based On Text

    Quote 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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    11th June 2006
    Location
    Derbyshire UK
    Posts
    115

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,284

    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
    VB:
    =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


  7. #7
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    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 at 22:45.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    2nd October 2006
    Posts
    326

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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 at 17:58.

  10. #10
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,284

    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


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Show 1 Of 2 Text Values Based On Another Cell
    By jeff45123 in forum Excel General
    Replies: 5
    Last Post: June 10th, 2008, 02:56
  2. Return Text Based On Text Existing In Range
    By youngsie81 in forum Excel General
    Replies: 4
    Last Post: October 15th, 2007, 08:41
  3. Multiple Text Box Values To One Text Box
    By m4r1u5 in forum Excel General
    Replies: 9
    Last Post: July 24th, 2007, 04:14
  4. Add Text Based on Part of Another Cell Text
    By fahim in forum Excel General
    Replies: 2
    Last Post: October 16th, 2006, 06:38
  5. Replies: 1
    Last Post: June 10th, 2004, 17:36

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno