Sum values in range of text strings based on other values

With text to column.

After that I run the macro below.

After that I made an pivot table of it.

See the attached file.

Please reply.

Sub CONVERTROWSTOCOL_Oeldere_revisted_with_steps_in_the_columns()

Dim rsht1 As Long, rsht2 As Long, i As Long, col As Long, col2 As Long, wsTest As Worksheet

'check if sheet "ouput" already exist

Const strSheetName As String = "Output"
 
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

   
   With Sheets("Output")
    .UsedRange.ClearContents
    .Range("A1:C1").Value = Array("item", "value", "Letter")
    
    End With
    
    
    rsht1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
    rsht2 = Sheets("Output").Range("A" & Rows.Count).End(xlUp).Row
    col = 2
    col2 = 3
    
    For i = 2 To rsht1
       
        Do While Sheets("sheet1").Cells(1, col).Value <> ""
            rsht2 = rsht2 + 1
            Sheets("Output").Range("A" & rsht2).Value = Sheets("sheet1").Range("A" & i).Value
            Sheets("Output").Range("B" & rsht2).Value = Sheets("sheet1").Cells(i, col).Value
            Sheets("Output").Range("C" & rsht2).Value = Sheets("sheet1").Cells(i, col2).Value
     
            col = col + 2
            col2 = col2 + 2
        Loop
        col = 2
        col2 = 3
    Next
  With Sheets("Output")
   
   .Range("C2:C" & .Rows.Count).SpecialCells(4).EntireRow.Delete
   Columns("A:Z").EntireColumn.AutoFit
    
    End With
End Sub

Is there a way to do this without them?

Maybe by others.

Try this array formula

=SUM(IFERROR(MID(B1:B4,FIND("P",B1:B4)-1,1),0)*1)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Change the highlighted portion for varied results

With the solution of Ace_XL in the file.

Money! thank you

Who is money ?