Count A characters in a cell As a given number

[crilly]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A
[/TD]
[TD]7
[/TD]
[TD]Count A characters in a cell As a given number Like ( A - As - 7 )
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]6
[/TD]
[TD]To return the like of 14 for A-A or 13 for A-B
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]5
[/TD]
[TD]so A- B- C in 3 cell add up to 18 and so on
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Why is A-B equal to 13 and A-B-C equal to 18? What haven't you told us about those other letters besides A?

 

[pbornemeier]

=LEN(C1)-LEN(SUBSTITUTE(UPPER(C1),UPPER(B1),"")) will give you the letter count. Not sure what else you are asking for.

 

[jimrward]

Can you give a sample of your data as well

 

[Peter_SSs]

See if this user-defined function is any use. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function mySum(s As String, rSubs As Range) As Long
  Dim Subs As Variant
  Dim i As Long
  
  Subs = rSubs.Value
  s = "-" & Replace(s, "-", "--") & "-"
  For i = 1 To UBound(Subs)
    s = Replace(s, "-" & Subs(i, 1) & "-", "-" & Subs(i, 2) & "-")
  Next i
  mySum = Evaluate(Replace(s, "-", "+0"))
End Function

Excel 2016
	A	B	C	D	E
1	A	7		A	7
2	B	6		A-A	14
3	C	5		A-B-C	18
4	D	4		A-B	13
5	E	3
6	F	2
7	G	1
8
Sub Values

Cell Formulas
Range	Formula
E1	=mySum(D1,A$1:B$7)

 

[FormR]

Hi, another option you can try that makes use of a technique that Rick posted here:

http://www.excelfox.com/forum/showthread.php/333-Get-Field-from-Delimited-Text-String

Note. This is hard coded for a maximum of 10 items in the criteria cell. This can be made more dynamic if needed.

Excel 2013/2016
	A	B	C	D	E
1	A	7		A	7
2	B	6		A-A	14
3	C	5		A-B	13
4	D	4		A-B-C	18
5	E	3
6	F	2
7	G	1
Sheet1

Cell Formulas
Range	Formula
E1	=SUMPRODUCT(SUMIFS(B:B,A:A,TRIM(MID(SUBSTITUTE(D1,"-",REPT(" ",99)),{1,2,3,4,5,6,7,8,9,10}*99-98,99))))

 

[crilly]

So at the end of the week and month I can get a total ofpoints for the person,

To get a (grade and point value )

A Has a Value of 7 - B Has a Value of 6 - C Has a Value of 5 - D Has a Value of 4



E Has a Value of 3 - F Has a Value of 2 - G Has a Value of 1



[TABLE="width: 489"]
<tbody>[TR]
[TD="width: 140, bgcolor: transparent"] Mon 22/07/2019
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"] D
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"]

46​

[/TD]
[/TR]
[TR]
[TD="width: 140, bgcolor: transparent"] Fri 26/07/2019
[/TD]
[TD="width: 64, bgcolor: transparent"] B
[/TD]
[TD="width: 64, bgcolor: transparent"] B
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"] B
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"] F
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"]

41​

[/TD]
[/TR]
[TR]
[TD="width: 140, bgcolor: transparent"] Sat 27/07/2019
[/TD]
[TD="width: 64, bgcolor: transparent"] B
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"] B
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"] F
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"]

42​

[/TD]
[/TR]
[TR]
[TD="width: 140, bgcolor: transparent"] Sun 28/07/2019
[/TD]
[TD="width: 64, bgcolor: transparent"] B
[/TD]
[TD="width: 64, bgcolor: transparent"] C
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"] E
[/TD]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"]

42​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, here is one option..:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Mon 22/07/2019	A	A	A	A	A	D	A	46
2	Fri 26/07/2019	B	B	A	B	A	F	A	41
3	Sat 27/07/2019	B	A	A	B	A	F	A	42
4	Sun 28/07/2019	B	C	A	A	A	E	A	42
Sheet1

Cell Formulas
Range	Formula
I1	=SUMPRODUCT(COUNTIF(B1:H1,{"A","B","C","D","E","F","G"}),{7,6,5,4,3,2,1})

 

[Rick Rothstein]

Hi, here is one option..:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Mon 22/07/2019	A	A	A	A	A	D	A	46
2	Fri 26/07/2019	B	B	A	B	A	F	A	41
3	Sat 27/07/2019	B	A	A	B	A	F	A	42
4	Sun 28/07/2019	B	C	A	A	A	E	A	42
Sheet1

Cell Formulas
Range	Formula
I1	=SUMPRODUCT(COUNTIF(B1:H1,{"A","B","C","D","E","F","G"}),{7,6,5,4,3,2,1})

Using the same setup you show, here is another formula for the OP to consider...

=SUMPRODUCT(72-CODE(B1:H1))