0

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.

enter image description here

braX
  • 11,506
  • 5
  • 20
  • 33
chocopapi175
  • 19
  • 2
  • Does this answer your question? [Is there an excel function for selecting an item inside a cell and then summing it across a column](https://stackoverflow.com/questions/72891445/is-there-an-excel-function-for-selecting-an-item-inside-a-cell-and-then-summing) – braX Jul 07 '22 at 03:40
  • No the last question did not account for a cell with multiple variables that have the same digit. – chocopapi175 Jul 07 '22 at 03:43
  • @chocopapi175 Try my updated formula to you this question. – Harun24hr Jul 07 '22 at 03:47
  • @chocopapi175 storing numeric data as strings, especially as a comma separated list, is really not how Excel is intended to work. While you _could_ write a formula to extract and process the values, you be much better off if you seperate the values into seperate columns – chris neilsen Jul 07 '22 at 05:45

1 Answers1

1

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() here from JvdV.

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36