I’m working with a spreadsheet that has product data including prices and category tags. Each row contains an item with its cost and a tags field that has multiple comma-separated values.
Sample data structure:
SKU - PRICE - CATEGORIES - VENDOR - SIZE
ABC123-PRO - 15.50 - MT, WIRES, PLASTIC - 8.2
DEF456-PRO - 22.75 - VALVE, METAL - 3.1
GHI789-XTR - 18500.99 - GOLD, MT, SOCKET, ACTIVE - 1250.80
I need to calculate the sum of all prices where the categories column contains “MT”. The expected result should be 18516.49 (combining first and third rows). I attempted using VLOOKUP(“MT”,A2:D4,2) but it returns zero. Also tried some filter approaches without success. What’s the right way to sum values based on partial text matches in Google Sheets?
try using SUMIF with wildcard patterns like =SUMIF(C:C,"*MT*",B:B) - the asterisks will match any text before and after MT in your categories column. works great for comma seperated tags
The SUMIFS function would work better here since you’re dealing with partial matches. Use =SUMIFS(B:B,C:C,"*MT*") where B:B is your price column and C:C is your categories column. I’ve used this approach extensively when working with product catalogs that have similar tag structures. The key difference from Luke’s suggestion is SUMIFS handles the criteria matching more reliably when you have mixed data types in adjacent columns. Make sure there are no extra spaces around your tags though - I learned this the hard way when my sums were coming up short because some entries had inconsistent spacing after commas.
Another approach that might help is using ARRAYFORMULA combined with SUMPRODUCT if you run into issues with the wildcard method. Something like =SUMPRODUCT((ISNUMBER(SEARCH("MT",C:C)))*B:B) can be more robust when dealing with inconsistent formatting. I’ve found this particularly useful when the tag data has irregular spacing or when you need to match multiple criteria simultaneously. The SEARCH function returns an error for non-matches which ISNUMBER converts to FALSE, making it work nicely with SUMPRODUCT for conditional summing. This method also handles cases where your target text might appear as part of a larger word, though that doesn’t seem to be your issue here.