Essentially I have a list of food items in one column and the various categories they fall into (contains meat, contains fish, is eaten cold, is eaten warm, is water) in another column. Food items can fall into various categories, and each category has its designated number, ranging from 1-15. To make things fun, lets suppose that when the designations were assigned, they were mixed (i.e., not placed in any particular order such as by ascending/ or descending number), but always following the format of #,# (with no spaces between numbers or commas). What formula can be used to 1) count all instances where there is a designated number within a column and 2)sum up these instances and output the total?

I asked a simplified version of this question earlier and got a good response. The suggested code was:

    =COUNTIF($C$3:$C$10,"*" & G3 & "*") COUNTIF($C$3:$C$10,G3)

However, this code incorrectly counts a cell with the numbers 15,11,3 for the "1"s.

CodePudding user response：

I post that answer based on your provided sample data. Now I have purified it to make it more flexible. Try this one

=SUM(--(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,SUBSTITUTE($C$3:$C$10,",","</s><s>"))&"</s></t>","//s[.=" & G3 & "]")<>""))

Details about FILTERXML()