We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

Strip prefix text and sum the value?

asked 2021-02-04 21:51:37 +0200

TimzLibCalc gravatar image

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?

edit retag flag offensive close merge delete

Comments

Make unambiguously clear what you mean by "prefix text".

Lupp gravatar imageLupp ( 2021-02-04 22:45:43 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2021-02-04 22:37:22 +0200

Lupp gravatar image

updated 2021-02-04 22:46:04 +0200

=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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-02-04 21:51:37 +0200

Seen: 22 times

Last updated: Feb 04