text: user9659: I have some  tsRNA count data in excel which I need to add selectively in blocks based on the count
  refID   Count
  1) tRNA-Ala-AGC-1   1
  tRNA-Ala-AGC-1  2
  tRNA-Ala-AGC-1  2
  tRNA-Ala-AGC-1  1
  tRNA-Ala-AGC-1  3
  tRNA-Ala-AGC-1  300
  tRNA-Ala-AGC-1  51
  tRNA-Ala-AGC-1  86
  tRNA-Ala-AGC-1  6
  tRNA-Ala-AGC-1  2
  tRNA-Ala-AGC-1  1
  tRNA-Ala-AGC-1  5
  
  2)tRNA-Ala-AGC-11 1
  tRNA-Ala-AGC-11 2
  tRNA-Ala-AGC-11 1
  tRNA-Ala-AGC-11 64
  tRNA-Ala-AGC-11 1
  tRNA-Ala-AGC-11 1
  tRNA-Ala-AGC-11 2
  tRNA-Ala-AGC-11 3
  tRNA-Ala-AGC-11 1
  tRNA-Ala-AGC-11 1
  tRNA-Ala-AGC-11 1
  
  3) tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  
  I am using sum and subtotal functions in excel to get the total counts of each of the blocks.
  I have about 22,000 values and several hundred groups like this.
  Is there a way to sum up all such blocks all at once based on the groups or at least a shorter way to sum things work other than autosum and subtotal?
  Any help will be useful.
  
  user3848: Suppose the header is on row 1 and starts at column 'A'. Then, for the above example, just point to a cell at say C1 to C3 and write ...
  =sum(B2:B12)
  =sum(B14:B25)
  =sum(B27:B38)
  
  This gives the subtotals.
  For the total sum point the mouse at D1 and write:
  =sum(B2:B38)
  
  ... except of course the real value is ...
  =sum(B2:B22000)
  
  In all cases you must put the = to start the cell.
  Its better to try out R, or Python pandas or just a Python list print (sum([1,2,3,4,5]) but Excel's okay.
  
  The issue is its slow, its taking hours to calculate the sum of 22 000 cells - thats not a lot even for Excel. In Python that wouldn't even exceed 1 second, literally. What you are facing is a RAM bottleneck - there is literally no other explanation.
  Two suggestions firstly, look at Windows Excel, particularly if you are using OSX. This is because historically Windows was much more RAM efficient than OSX. Now however, I don't know. Secondly, simply switch to a desktop with 4 Gig RAM or more. Literally thats all it will take.
  The drop back position is to do the calculation in two halves on the low RAM machine, i.e. split the data set in two. Not a cool solution but it will probably work. The final solution is to look at the stuff below the line.
  
  The solutions are to shift to VB which none of us code in these days, but its the code underneath Excel, or the alternative is to write a custom script in a different language that will do the adding such as Python, R ... even bash.
  
original_text: 9659: <p>I have some  tsRNA count data in excel which I need to add selectively in blocks based on the count</p>
  <pre><code>refID   Count
  1) tRNA-Ala-AGC-1   1
  tRNA-Ala-AGC-1  2
  tRNA-Ala-AGC-1  2
  tRNA-Ala-AGC-1  1
  tRNA-Ala-AGC-1  3
  tRNA-Ala-AGC-1  300
  tRNA-Ala-AGC-1  51
  tRNA-Ala-AGC-1  86
  tRNA-Ala-AGC-1  6
  tRNA-Ala-AGC-1  2
  tRNA-Ala-AGC-1  1
  tRNA-Ala-AGC-1  5
  
  2)tRNA-Ala-AGC-11 1
  tRNA-Ala-AGC-11 2
  tRNA-Ala-AGC-11 1
  tRNA-Ala-AGC-11 64
  tRNA-Ala-AGC-11 1
  tRNA-Ala-AGC-11 1
  tRNA-Ala-AGC-11 2
  tRNA-Ala-AGC-11 3
  tRNA-Ala-AGC-11 1
  tRNA-Ala-AGC-11 1
  tRNA-Ala-AGC-11 1
  
  3) tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  tRNA-Ala-AGC-3
  </code></pre>
  <p>I am using sum and subtotal functions in excel to get the total counts of each of the blocks.
  I have about 22,000 values and several hundred groups like this.
  Is there a way to sum up all such blocks all at once based on the groups or at least a shorter way to sum things work other than autosum and subtotal?</p>
  <p>Any help will be useful.</p>
   3848: <p>Suppose the header is on row 1 and starts at column 'A'. Then, for the above example, just point to a cell at say <code>C1</code> to <code>C3</code> and write ...</p>
  <pre><code>=sum(B2:B12)
  =sum(B14:B25)
  =sum(B27:B38)
  </code></pre>
  <p>This gives the subtotals.</p>
  <p>For the total sum point the mouse at <code>D1</code> and write:</p>
  <pre><code>=sum(B2:B38)
  </code></pre>
  <p>... except of course the real value is ...</p>
  <pre><code>=sum(B2:B22000)
  </code></pre>
  <p>In all cases you must put the <code>=</code> to start the cell.</p>
  <p>Its better to try out <code>R</code>, or Python <code>pandas</code> or just a Python list <code>print (sum([1,2,3,4,5])</code> but Excel's okay.</p>
  <hr />
  <p>The issue is its slow, its taking hours to calculate the sum of 22 000 cells - thats not a lot even for Excel. In Python that wouldn't even exceed 1 second, literally. What you are facing is <strong>a RAM bottleneck</strong> - <em>there is literally no other explanation</em>.</p>
  <p>Two suggestions firstly, look at Windows Excel, particularly if you are using OSX. This is because historically Windows was much more RAM efficient than OSX. Now however, I don't know. Secondly, simply switch to a desktop with 4 Gig RAM or more. Literally thats all it will take.</p>
  <p>The drop back position is to do the calculation in two halves on the low RAM machine, i.e. split the data set in two. Not a cool solution but it will probably work. The final solution is to look at the stuff below the line.</p>
  <hr />
  <p>The solutions are to shift to <code>VB</code> which none of us code in these days, but its the code underneath Excel, or the alternative is to write a custom script in a different language that will do the adding such as <code>Python</code>, <code>R</code> ... even <code>bash</code>.</p>
  