title: Excel: divide a number by the number of times a ";" appears in a cell that contains ";" and a "word" in a range
body: <p>I would like to select cells in a range that contains ";" and either of two words "SUN" or "SUNNY" and then I would like to divide a number by the number of times ";" appears in those cells, e.g.. if there are 4 cells containing ";" and either "SUN" or "SUNNY" and each one of those cells contains ";" 2 times, then it should divide the number by 8.
  Is this possible in excel? perhaps using two or more separated formulas? or cells?</p>
  
  <p>Here is what I have made so far:</p>
  
  <pre><code>COUNTIFS(DB3:DB50,DC26,DB3:DB50,$D$1)
  </code></pre>
  
  <p>to count if "SUN" or "SUNNY" appear</p>
  
  <pre><code>=(LEN(I3)-LEN(SUBSTITUTE(I3,";",""))
  </code></pre>
  
  <p>to "count" the number of times ";" appears, but, by using this formula I have to search within an specific cell "I3", I would to search within cells that contain ";" and either of two words "SUN" or "SUNNY"</p>
  
  <p>I need this formula above to do the same, but in a range of cells (all rows of column A) not only within an specific cell (A1 or A2, etc.) Is this possible?</p>
  
  <p>I really need to avoid repeating the formula in each row of column B because every column will be looking for a different couple of words (eg. SUN SUNNY, CLOUD, CLOUDY, RAIN, RAINY).</p>
  
  <p>For example:</p>
  
  <p><img src="https://i.stack.imgur.com/hNbfX.jpg" alt="enter image description here"></p>
  
  <p>Then I would just sum column C, is there a way to achieve this??</p>