CODEWITHSUNDEEP
excelexcel-formula
Tushar
Tushar

Reputation: 23

Conditional Sum of alphanumeric cells

Suppose i have following data in excel column

E20
EW5
E30
EW7

I want total as shown below

E = 50
EW = 12

Upvotes: 2

Views: 94

Answers (1)

Naresh
Naresh

Reputation: 3034

Following are the array (CSE) formulas in B3 & B4. Copy the formula; select the cell; press F2; paste the formula and press Control+Shift+Enter (CSE)

=SUM(IFERROR(SUBSTITUTE(A3:A6,"E","",1)*1,0)) =SUM(IFERROR(SUBSTITUTE(A3:A6,"EW","",1)*1,0))

enter image description here

If you want the totals to be shown as EW = 12 then, "EW = "&SUM(IFERROR(SUBSTITUTE(A3:A6,"EW","",1)*1,0)) is CSE formula .

EDIT

The above formula will treat "EW5" and "5EW" as same. So, if we add "10EW" in the above sum range then the total will be 22 and not 12.

However, if we want to sum only the starting with EW then we can use LEFT function in array formula as below and the total will be 12 and not 22 even after adding "10EW" to the sum range.

=SUM(IFERROR(SUBSTITUTE(IF(LEFT(A3:A6,2)="EW",A3:A6,""),"EW","")*1,0))

Upvotes: 1

Related Questions