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:


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?

1 Answer 1


You could do a "fix" with SUBSTITUTE fx:



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)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.