CODEWITHSUNDEEP
excel
Koosh
Koosh

Reputation: 896

trying to sum up a column, but some columns contain a character which needs to be there

I have a spreadsheet with a column that contains * next to some of the numbers. That * is necessary and needs to stay there. Is there anyway to sum up a column like this:

 100*
 100
 50
 10*

 260

Upvotes: 0

Views: 30

Answers (1)

PaichengWu
PaichengWu

Reputation: 2689

Array Formula

If there is only one character following the number, please use the array formula below:

=SUM(IF(ISTEXT(A1:A4),--LEFT(A1:A4,LEN(A1:A4)-1),A1:A4))

press ctrl+shift+enter

SUMPRODUCT function

I modify @Koosh's formula in comment.

=SUMPRODUCT(--SUBSTITUTE(A1:A4,"*",""))

Upvotes: 2

Related Questions