Split numbers from letters in row and count

[Olaf Jacobs]

Hi all,

I'm searching for some formula/vba kind of coding to filter information given in an row.

The row is setup to represent an year and for each day (represented by single cell) there will be an short little bit of information given an letter to refer to an project and an number 0 till 24 to represent the amount of hours for this project.

Now the result I'm looking for is to gather in the first column (A) the sum of hours spend in total per row, so filter out all numbers and sum them.
And in the first row's (1 till 10) i would like to sum the letters separated per row.

I was looking in to regular formulas and nothing really worked out like i had in mind, i was getting numbers and letters split from each other but not counting up and separating them etc, so I'm hoping there is some smarter people then me here on the forum.

Regards Olaf.

 

[lrobbo314]

To sum the numbers

=SUM(IF(B11:O11<>"",RIGHT(B11:O11,LEN(B11:O11)-1))+0)

That's an array formula so use Ctrl+Shift+Enter.

Then to count the letters

=SUMPRODUCT(--(LEFT(B$11:B$22,1)="A"))

Change the formula for each letter on each new row.

And, adjust your ranges as necessary.

 

[Olaf Jacobs]

I Don't know what to say I'm dazzled, Thanks a lot this is working 100%

 

[jtakw]

Hi,

If you change A1:A10 to house Only the Alphabets A to J, then use this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	A	1	1	1	1	1	2	2	2	2	1	1	1	1	1
2	B										1	1	1
3	C								1	2	2	2	1	1	1
4	D							1	1	1
5	E									1	1	1	1	1	1
6	F
7	G
8	H
9	I
10	J
11	162	A6	A12	A12	A12	A12	A12	A12	A12	A12	A12	A12	A12	A12	A12
12	31						A4	A12	A11	A4
13	30										B6	B11	B5	C3	C5
14	45											E12	E11	E11	E11
15	42									C12	C11	C11	C8
16	35							D12	D18	D5
17	9									E1	E8
18	0
19	27								C5	C9	C11	C2
Sheet673

Cell Formulas
Range	Formula
B1	=IFERROR(1/(1/COUNTIF(B$11:B$100,$A1&"*")),"")
A11	=SUMPRODUCT(--(MID(B11:O11&"000",2,99)))/1000

If you need to leave it like the way you show in your sample above, use this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Amount of "A"	1	1	1	1	1	2	2	2	2	1	1	1	1	1
2	Amount of "B"										1	1	1
3	Amount of "C"								1	2	2	2	1	1	1
4	Amount of "D"							1	1	1
5	Amount of "E"									1	1	1	1	1	1
6
7
8
9
10
11	162	A6	A12	A12	A12	A12	A12	A12	A12	A12	A12	A12	A12	A12	A12
12	31						A4	A12	A11	A4
13	30										B6	B11	B5	C3	C5
14	45											E12	E11	E11	E11
15	42									C12	C11	C11	C8
16	35							D12	D18	D5
17	9									E1	E8
18	0
19	27								C5	C9	C11	C2
Sheet673 (3)

Cell Formulas
Range	Formula
B1	=IFERROR(1/(1/COUNTIF(B$11:B$100,CHAR(ROW()+64)&"*")),"")
A11	=SUMPRODUCT(--(MID(B11:O11&"000",2,99)))/1000

Either way,
B1 formula copied down to B10 and across as far as needed
A11 formula copied down to A100

B1 formula will Not show 0 (zeros) for cells with no data, if 0 (zeros) are acceptable, formula can be a bit shorter.

Edit: All formulas normally entered.

 

[DanteAmor]

I show you a couple of additional ideas.
Copy the formula of B1 to the right and then down.


The second formula, from cell A11 is array formula, copy down.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:43.72px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#b8cce4; ">A</td><td style="background-color:#dbeef3; text-align:right; ">1</td><td style="background-color:#dbeef3; text-align:right; ">1</td><td style="background-color:#dbeef3; text-align:right; ">1</td><td style="background-color:#dbeef3; text-align:right; ">1</td><td style="background-color:#dbeef3; text-align:right; ">2</td><td style="background-color:#dbeef3; text-align:right; ">2</td><td style="background-color:#dbeef3; text-align:right; ">2</td><td style="background-color:#dbeef3; text-align:right; ">2</td><td style="background-color:#dbeef3; text-align:right; ">1</td><td style="background-color:#dbeef3; text-align:right; ">1</td><td style="background-color:#dbeef3; text-align:right; ">1</td><td style="background-color:#dbeef3; text-align:right; ">1</td><td style="background-color:#dbeef3; text-align:right; ">1</td><td style="background-color:#dbeef3; text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#b8cce4; ">B</td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; ">1</td><td style="background-color:#dbeef3; text-align:right; ">1</td><td style="background-color:#dbeef3; text-align:right; ">1</td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#b8cce4; ">C</td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; ">1</td><td style="background-color:#dbeef3; text-align:right; ">1</td><td style="background-color:#dbeef3; text-align:right; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#b8cce4; ">D</td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#b8cce4; ">E</td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#b8cce4; ">F</td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#b8cce4; ">G</td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#b8cce4; ">H</td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#b8cce4; ">I</td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#b8cce4; ">J</td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td><td style="background-color:#dbeef3; text-align:right; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#92d050; text-align:right; ">162</td><td style="background-color:#d7e4bc; ">A6</td><td style="background-color:#d7e4bc; ">A12</td><td style="background-color:#d7e4bc; ">A12</td><td style="background-color:#d7e4bc; ">A12</td><td style="background-color:#d7e4bc; ">A12</td><td style="background-color:#d7e4bc; ">A12</td><td style="background-color:#d7e4bc; ">A12</td><td style="background-color:#d7e4bc; ">A12</td><td style="background-color:#d7e4bc; ">A12</td><td style="background-color:#d7e4bc; ">A12</td><td style="background-color:#d7e4bc; ">A12</td><td style="background-color:#d7e4bc; ">A12</td><td style="background-color:#d7e4bc; ">A12</td><td style="background-color:#d7e4bc; ">A12</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#92d050; text-align:right; ">31</td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; ">A4</td><td style="background-color:#d7e4bc; ">A12</td><td style="background-color:#d7e4bc; ">A11</td><td style="background-color:#d7e4bc; ">A4</td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#92d050; text-align:right; ">30</td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; ">B6</td><td style="background-color:#d7e4bc; ">B11</td><td style="background-color:#d7e4bc; ">B5</td><td style="background-color:#d7e4bc; ">C3</td><td style="background-color:#d7e4bc; ">C5</td><td style="background-color:#d7e4bc; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=COUNTIF(B$11:B$20,$A1&"*")</td></tr><tr><td >A11</td><td >{=SUM(IFERROR(MID(B11:Z11,2,2)+0,0))}</td></tr></table></td></tr></table>

 

[JoeMo]

If you want a VBA solution, this works with the layout you showed in your OP.

Sub Olaf()
Dim Rin As Range, Vin As Variant, Rct As Range, Vct As Variant, Vsum() As Variant, lastRw As Long, lastCol As Long
Dim i As Long, j As Long, S As Long
lastRw = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastCol = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set Rin = Range(Cells(11, "B"), Cells(lastRw, lastCol))
Vin = Rin.Value
ReDim Vsum(1 To UBound(Vin, 1))
For i = 1 To UBound(Vin, 1)
    For j = 1 To UBound(Vin, 2)
        If Vin(i, j) = "" Then
            S = S + 0
        Else
            S = S + Val(StrReverse(Val(StrReverse(Vin(i, j)))))
        End If
    Next j
    Vsum(i) = S
    S = 0
Next i
Range(Cells(11, "A"), Cells(lastRw, "A")).Value = Application.Transpose(Vsum)
Set Rct = Range(Cells(1, "B"), Cells(10, lastCol))
ReDim Vct(1 To Rct.Rows.Count, 1 To Rct.Columns.Count)
For i = 1 To UBound(Vct, 1)
    For j = 1 To UBound(Vct, 2)
        Vct(i, j) = Application.CountIf(Rin.Columns(j), Chr(64 + i) & "*")
    Next j
Next i
Rct.Value = Vct
End Sub

 

[Olaf Jacobs]

Now to make things complicated is there a way to count colour instead of the letter like in the example ?

I know now I'm pushing it



Any how that i didn't figure this out, I kept staring on the simple formula's and didn't see the puzzle

 

[Olaf Jacobs]

So back to the sheet again,

I found this handy function to count cells based on colour this is replacing the count of the letters in the array so i only heve to apply numbers in cells

Function CountCcolor(range_data As Range, criteria As Range) As Long
    Dim datax As Range
    Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
        CountCcolor = CountCcolor + 1
    End If
Next datax
End Function

And to admit this is actually not an bad way to solve my sheet, if i can select cell/cells and set one information tag with colour and in the other information tag with an number then that should work also, cells with only numbers are easier to sum (to make the sheet dummy proof)
But now the trouble is starting, this function is not up dating the calculation, after changing or adding an colour to an range of cells the count is not changing. What am i doing wrong here ?