sum of numbers with a specific letter

M

mculver13

New Member
Joined
Jan 14, 2019
Messages
8
I am attempting to get a combined total for a range of cells that contain a number and letter. Example 8v+3v with it giving the answer of 8v. Any ideas?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
A1: 8v+3v
B1: =SUMPRODUCT(--(0&MID(A1,ROW($1:$100),TEXT(FREQUENCY(-ROW($1:$99),-ISERR(-MID(0&A1,ROW($1:$99),1))*ROW($1:$99))-1,"[<]\0"))))&"v"
Result: 8v
 
Upvote 0
sorry I am new to excel so this coding isn't working or how do I decipher it. I need cell C6 to have the combined total. I need it to add all the numbers with a "v" from Cells H6 to ASL6. how would I write the correct formula in cell C6?
 
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 939"]
<colgroup><col width="149" style="width: 112pt; mso-width-source: userset; mso-width-alt: 4768;"> <col width="167" style="width: 125pt; mso-width-source: userset; mso-width-alt: 5344;"> <col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3424;" span="4"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 2976;"> <col width="69" style="width: 52pt;" span="6"> <tbody>[TR]
[TD="width: 149"]Last Name[/TD]
[TD="width: 167"]First Name
[/TD]
[TD="width: 107"]Vacation[/TD]
[TD="width: 107"]Personal[/TD]
[TD="width: 107"]FMLA[/TD]
[TD="width: 107"]Absent[/TD]
[TD="width: 93"]Tardy[/TD]
[TD="width: 69"]1/1[/TD]
[TD="width: 69"]1/2[/TD]
[TD="width: 69"]1/3[/TD]
[TD="width: 69"]1/4[/TD]
[TD="width: 69"]1/5[/TD]
[TD="width: 69"]1/6[/TD]
[/TR]
[TR]
[TD="width: 149"]doe[/TD]
[TD="width: 167"]john[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1v[/TD]
[TD]8v[/TD]
[TD]4f[/TD]
[TD] [/TD]
[TD]a[/TD]
[TD]4p[/TD]
[/TR]
[TR]
[TD="width: 149"] [/TD]
[TD="width: 167"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="width: 149"] [/TD]
[TD="width: 167"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="width: 149"] [/TD]
[TD="width: 167"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="width: 149"] [/TD]
[TD="width: 167"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="width: 149"] [/TD]
[TD="width: 167"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="width: 149"] [/TD]
[TD="width: 167"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
as example with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Last Name[/td][td=bgcolor:#5B9BD5]First Name[/td][td=bgcolor:#5B9BD5]Vacation[/td][td=bgcolor:#5B9BD5]Personal[/td][td=bgcolor:#5B9BD5]FMLA[/td][td=bgcolor:#5B9BD5]Absent[/td][td=bgcolor:#5B9BD5]Tardy[/td][td=bgcolor:#5B9BD5]1/1[/td][td=bgcolor:#5B9BD5]1/2[/td][td=bgcolor:#5B9BD5]1/3[/td][td=bgcolor:#5B9BD5]1/4[/td][td=bgcolor:#5B9BD5]1/5[/td][td=bgcolor:#5B9BD5]1/6[/td][td][/td][td=bgcolor:#70AD47]Addition[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]doe[/td][td=bgcolor:#DDEBF7]john[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7]1v[/td][td=bgcolor:#DDEBF7]8v[/td][td=bgcolor:#DDEBF7]4f[/td][td=bgcolor:#DDEBF7]7v[/td][td=bgcolor:#DDEBF7]a[/td][td=bgcolor:#DDEBF7]4p[/td][td][/td][td=bgcolor:#E2EFDA]16v[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]
0​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]
0​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]
0​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Transposed Table",{{"Column1", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Contains([Column1], "v")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns","v","",Replacer.ReplaceText,{"Column1"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1", Int64.Type}}),
    #"Transposed Table1" = Table.Transpose(#"Changed Type2"),
    #"Inserted Sum" = Table.AddColumn(#"Transposed Table1", "Addition", each List.Sum({[Column1], [Column2], [Column3]}), type number),
    #"Added Suffix" = Table.TransformColumns(#"Inserted Sum", {{"Addition", each Text.From(_, "en-GB") & "v", type text}}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Suffix",{"Addition"})
in
    #"Removed Other Columns1"[/SIZE]
 
Upvote 0
what is a problem with read footnote?

post you excel version: 95, 98, 2000, 2002, 2003, 2007, 2010, 2013, 2016, 365, 2019 ?
create sample file with representative example of raw data and expected result
use dropbox, google drive, one drive or any similar to share this excel file
post here link to this file
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Sum of Row with Letters and Merged Numbers in Different Cells
Replies
3
Views
248
Joe4
Joe4
D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
519
Davenil
D
dcofer
  • Solved
Locating Words With Specific Letters in a Column
Replies
5
Views
652
dcofer
dcofer
M
  • Question Question
assigning one of three values to cell to get a score of the row
Replies
1
Views
133
dreid1011
dreid1011
B
  • Solved
VLOOKUP issue with wildcard parameter for partial matches in number/letter combinations
2
Replies
10
Views
753
BrooksProctor
B

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top