I’m working with a spreadsheet that has columns for price, product category, and keyword tags. I need to add up all the prices where the tags column contains a specific keyword.
Sample data structure:
SKU - PRICE - KEYWORDS - VENDOR - SIZE
ABC123-PRO - 45.50 - METAL, WIRES, ABS - 12.1
DEF456-PRO - 22.85 - VALVE, BRASS - 8.3
GHI789-MAX - 18950.75 - GOLD, METAL, CONNECTOR, ACTIVE - 1847.60
I want to search for the keyword “METAL” and get back 18996.25 (sum of first and third rows). I’ve been trying VLOOKUP(“METAL”,A2:D4,2) but it keeps returning zero. The filter function isn’t working either. What’s the right approach for this kind of partial text matching and summing?
Have you tried using ARRAYFORMULA combined with IF and FIND functions? Something like =SUM(ARRAYFORMULA(IF(ISNUMBER(FIND("METAL",C2:C4)),B2:B4,0))) works really well for this type of scenario. I ran into a similar issue when analyzing inventory data and found that this approach handles partial matches more reliably than some other methods. The FIND function returns an error when the text isn’t found, so wrapping it in ISNUMBER converts that to TRUE/FALSE, then IF returns either the price or zero. One thing to watch out for though - make sure your keyword search is case sensitive or not depending on your needs. FIND is case sensitive while SEARCH isn’t, so you might want to use SEARCH instead of FIND if your data has mixed capitalization.
vlookup wont work here becuase it needs exact matches. try using SUMIF with wildcards instead - something like =SUMIF(C:C,"*METAL*",B:B) should do the trick. the asterisks make it search for partial matches within the cell.
The SUMIF function with wildcards is correct, but you might also want to consider SUMIFS if you need multiple criteria later. Another approach that works well is using SUMPRODUCT with SEARCH function: =SUMPRODUCT((NOT(ISERROR(SEARCH("METAL",C:C))))*B:B). This formula searches for the text within each cell and multiplies the corresponding price values. The advantage is that it handles case sensitivity better and gives you more control over the matching logic. I’ve used this method extensively when dealing with product catalogs where keywords aren’t standardized. Just make sure your data range is consistent - if you’re referencing full columns like C:C, do the same for the sum range.