Numbers With Letter Prefixes - Need to Add Them Together If I\'m running a small

Question

Numbers With Letter Prefixes - Need to Add Them Together

If I'm running a small business and my products have SKU codes like: a bag of 6 Apples is APL006, a bag of 12 apples is APL012. So when I get a list of everything ordered that day, I see things like

APL006

APL006

APL012

APL012

ORA009

ORA009

(...)

I'd like to instead be able to see one APL number, in this case APL036. How can I do a SUM with those codes, without the 'APL' part causing a problem?

Harder still, how would I get it to avoid including the ORA (oranges) numbers too, and instead get a separate number for those?

Too much for one question? :)

Thanks!

Explanation / Answer

Hi,

The easiest way is to make a list of letter codes (APL, ORA, ...)

With your data in A1:A6 (adapt to your needs)

In C1:C2 APL, ORA ...

In D1: =SUM(--(LEFT($A$1:$A$7;3)=C1)*IF($A$1:$A$7<>"";RIGHT($A$1:$A$7;3);0))

This is an array formula, enter it with CTRL+SHIFT+ENTER,

it will appear in the formula bar as:

{=SUM(--(LEFT($A$1:$A$7;3)=C1)*IF($A$1:$A$7<>"";RIGHT($A$1:$A$7;3);0))}

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.