title: Adding the first digit(s) of a cell ending with specific letter
body: <p>I have a sheet that has blocks of cells. I add them for a total if they don't contain any letter. Letters signify a specific variable or signifier code for tracking. I also want to add the cell value when a cell has an ending code letter. I've tried SUMIF, Substitute, SumProduct and a few others.</p>
  <p>For a while I've used the following which worked till the S value changed to another number than &quot;8&quot; which then gave the wrong sum from the range.</p>
  <p><code>=if(countif(D64:Q64,&quot;*S&quot;)=0,&quot;&quot;,((countif(D64:Q64,&quot;*S&quot;))*8))</code></p>
  <p>In the cell range I have 5 variable groups ending in a letter (A, H, S, C and R) and one group not ending in any letter.</p>
  <p>The formula I use to add the cells not containing a letter but have a value is this</p>
  <p><code>=IF(SUM(AA64,(SUMIF(D65:Q65,&quot;&lt;&gt;&quot;)),-(COUNTIF(D65:Q65,&quot;&gt;=0&quot;)*8))&gt;24,24,(SUM(AA64,(SUMIF(D65:Q65,&quot;&lt;&gt;&quot;)),-(COUNTIF(D65:Q65,&quot;&gt;=0&quot;)*8))))</code></p>
  <p>which adds the table data plus the previous lines table data but does not exceed 24.</p>
  <p>H is a set value of 6 and doesn't change.</p>
  <p>Does anyone know how to add the value of the cells that contain a specific letter?</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th><em>N</em> (number)</th>
  <th>A</th>
  <th>S</th>
  <th>C</th>
  <th>R</th>
  <th>H</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>8.5</td>
  <td>H</td>
  <td>8A</td>
  <td>2S</td>
  <td>9</td>
  <td>3C</td>
  <td>0.5R</td>
  <td>17.5</td>
  <td>8</td>
  <td>2</td>
  <td>3</td>
  <td>0.5</td>
  <td>6</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I'd prefer it be a formula usable in Google Sheets because that is where this data is.</p>