Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Sum Values Based On Text

1. Established Member
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. Super Moderator
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. Established Member
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. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

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

Excel Video Tutorials / Excel Dashboards Reports

5. Senior Member
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

Excel Video Tutorials / Excel Dashboards Reports

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

7. Super Moderator
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. Established Member
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. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

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

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

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