Sumproduct using wildcard

[goldsam120]

I am attempting to use sumproduct to total how many times certain abbreviated terms show up in a single column. Sometimes there is only one term, other times there are a string of terms separated by a slash (/) mark. The terms are H, MED, DRUG, ALC, OTC, and UNK. For instance one cell might be only H, but other times it might be H/MED, or even H/MED/ALC. I was interested in using sumproduct, only because I have the data set in a table and I might need to limit my data based on certain years not the entire data set (if that makes sense).

Currently, I have =SUMPRODUCT(SUBTOTAL(3,OFFSET($J:$J,ROW($J:$J)-MIN(ROW($J:$J)),,1))*($J:$J="H")) as the first rule, but it only add H when it is alone. The typical wildcard (* or ?) has not worked for me. I have tried =SUMPRODUCT(SUBTOTAL(3,OFFSET($J:$J,ROW($J:$J)-MIN(ROW($J:$J)),,1))*($J:$J="*/H")) and =SUMPRODUCT(SUBTOTAL(3,OFFSET($J:$J,ROW($J:$J)-MIN(ROW($J:$J)),,1))*($J:$J="H/*")) to sum anytime H shows up, but I have been unsuccessful.

Please consider helping me. Thank you!

 

[mabalo76]

Try this

=SUMPRODUCT(--(ISNUMBER(SEARCH("H",J:J))))

 

[goldsam120]

=SUMPRODUCT(--(ISNUMBER(SEARCH("h",J:J))))

This totals the column correctly but when I remove certain years from the table, it still accounts for the full table with all the years. Thank you for the response.