Use LEFT function in SUMIF or SUMIFS

Question

I am trying to total up the quantities in Column E base on the first 4 characters of Column D.  I see online that the LEFT function can't be used in SUMIF or SUMIFS.  Is there another formula I can try?

D	E
Item Name	Trans Qty
.006 x 1.331 SS 321 STRIP (PER SPEC MS-CHS-S-001P)	221
.006 x 1.331 SS 321 STRIP (PER SPEC MS-CHS-S-001P)	221
.006 x 1.331 SS 321 STRIP (PER SPEC MS-CHS-S-001P)	606
.006 x 1.890 SS 321 STRIP (PER SPEC MS-CHS-S-001P)	780
.008 x 3.701 SS 321 STRIP (PER SPEC MS-CHS-S-001P)	2530
.008 x 3.701 SS 321 STRIP (PER SPEC MS-CHS-S-001P)	2264
.009 x 4.815 SS 321 STRIP (PER SPEC MS-CHS-S-001P)	1816
.009 x 4.815 SS 321 STRIP (PER SPEC MS-CHS-S-001P)	2605

Answer 1

H2: =SUMIF(D:D,G2&"*",E:E)

and drag down.

Andreas.

Answer 2

Thank you, Andreas!  I was hoping to find an all-in-one formula, but this works just as well.

Answer 3

A All-In-One solution is possible using Power Query.

https://support.office.com/en-us/article/get-tr...

But in PQ you also have to get the left 4 chars, then you can group by that chars.

https://support.office.com/en-us/article/group-...

Andreas.