Sum of values ​​with strings

Question

I have several columns that contain sample information, as follows:

A | B | C | D ------------------------ 23R | 12C | 4D | 35R 12C | 76T | 14T | 19D 32C | 56D | 14R | 68D ... | ... | ... | ... etc | etc | etc | etc 

From the whole table (without creating another column) I would like to summarize all the values ​​containing the letter "R". In the above table of examples, the number should be: 72. I tried many different approaches:

• Performing a direct sum will give "0" (as expected)
• Tried to split a letter from a number using the LEFT / RIGHT / LEN combination, but it needed a different column
• Using SUMIF will only work with numbered ranges (unless someone shows me to use the function in advance without an extra column)
• SUMPRODUCT did not help
• Looked at vlookup / hlookup. They did not give the desired results.

How can I achieve this without any extra columns? Cells can also be empty, and I would like to treat them as zeros, if possible.

Answer 1

Use this array formula:

 =SUM(IF(RIGHT(A2:D4,1)="R",VALUE(LEFT(A2:D4,LEN(A2:D4)-1)),0)) 

A2: D4 is your whole table. Enter the formula by pressing Ctrl + Shift + Enter.

Answer 2

Excellll has provided a good formula for this. Here is a VBA function that will do the same. You would call it like this:

 =CountR(A2:D4) 'in your example becomes 72 

code:

 Function CountR(ByVal cell_range As Range) As Long Dim i As Long, j As Long, total As Long Dim varray As Variant varray = cell_range.Value For i = 1 To UBound(varray, 1) For j = 1 To UBound(varray, 2) If Right$(varray(i, j), 1) = "R" Then total = total + Left$(varray(i, j), Len(varray(i, j)) - 1) End If Next Next CountR = total End Function