How to SUMIFS text beginning with apostrophe

Question

I am receiving a list of products from an external data source. One of the product names begins with an apostrophe. When I attempt to produce a summary of the products and total quantity using UNIQUE() and SUMIFS() the formula is returning zero for the product that begins with an apostrophe.

I have made an example sheet here:

https://docs.google.com/spreadsheets/d/191bqjUPPPtt5ZKeTw7pUplsve-tBdDBhpBV0Ic3e-nA/edit?usp=sharing

Interestingly, a VLOOKUP will work fine, but that will miss additional entries in the list.

Does anyone have any ideas on how to solve this without removing the leading apostrophe?

Answer 1

You could do a "fix" with SUBSTITUTE fx:

=ARRAYFORMULA(SUMIFS(B:B, SUBSTITUTE(A:A, "'", ""), SUBSTITUTE(D2, "'", "")))

Or use QUERY and have it all in one go:

=QUERY(A1:B, "select A,sum(B) where A !='' group by A label sum(B)'Qty'", 1)