How do I write a formula that does something like this:
ITEM 1 8
ITEM 2 7
ITEM 3 C1
ITEM 4 3
ITEM 5 C2
ITEM 6 C7
TOTAL = 18 (NUMERICAL)
“C” TOTAL = 10 ←-- How to get that? What formula, please?
Make unambiguously clear what you mean by “prefix text”.
=SUMPRODUCT(IFERROR(VALUE(REGEX(A1:A6;"^( *)(\d+)( *)$";"$2";1));0))
=SUMPRODUCT(IFNA(VALUE(REGEX(A1:A6;"(?<=C)\d+$";;1));0))
or similar.
Needing LibO V 6.2 or higher.