<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>

CC BY-SA 4.0
2022-10-12T03:41:41.573Z
I have already done that but since there are so many cells I have to do the sums as you suggested for each subgroup individually and it takes several hours to do such sums, hence I am looking for a quick work around.
Aranyak Goswami
CC BY-SA 4.0
2022-10-12T15:27:43.303Z
Hi @AranyakGoswami what you are up against is a RAM bottleneck. Excel shouldn't have trouble with the calculation on a standard desktop.
M__
CC BY-SA 4.0
2022-10-13T23:34:38.327Z
I suspect @AranyakGoswami is saying that it takes several hours to select regions around the counts for each subgroup, and add the associated formula into the correct cell near the gene counts. At about 1 minute per gene, several hours could mean about 10,000 genes.
gringer
CC BY-SA 4.0
2022-10-12T03:35:26.037Z
2022-10-12T15:26:29.667Z
2022-10-12T15:26:29.667Z
M__
<p>It looks like what you want to do is a pivot table, which provides summary statistics for fields, grouped by specified categories. As a prerequisite, this requires an input dataset that is well formatted; more generally:</p>
<ul>
<li>Put each observation or sample in its own row; don't leave row gaps between samples or groups. Leaving gaps makes bulk analysis more difficult and time consuming. It can still be done, it just means that the data needs to be pre-cleaned, or alternatively analysed.</li>
<li>Put all your variables in columns - the thing that vary between samples, like ‘strain’ or ‘DNA-concentration’</li>
<li>Have column names be explanatory, but without spaces</li>
<li>Do not combine multiple pieces of information in one cell</li>
</ul>
<p>For more information, see this Data Carpentries Workshop on the recommended way to structure data in spreadsheets:</p>
<p><a href="https://datacarpentry.org/organization-genomics/01-tidiness/index.html#structuring-data-in-spreadsheets" rel="nofollow noreferrer">https://datacarpentry.org/organization-genomics/01-tidiness/index.html#structuring-data-in-spreadsheets</a></p>
<p>If the data is prepared in this way, then selecting the whole table and creating a pivot table should work. Unfortunately, the programs keep changing where in the menu that can be found. Currently in LibreOffice, it's <code>Data</code> -&gt; <code>Pivot Table</code> -&gt; <code>Insert or Edit...</code>, wheareas on my version of Excel on Mac OSX, it's found at <code>Data</code> -&gt; <code>Summarise With Pivot Table</code>.</p>
<p>This will load up a visual interface that allows you to drag and drop fields to summarise. For this data, you'd probably want <em>RefID</em> in rows, and <em>sum(Count)</em> as the data field:</p>
<p><a href="https://i.sstatic.net/SrROh.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/SrROh.png" alt="Pivot table customisation dialog, showing RefID in rows, and sum(Count) as the data field" /></a></p>
<p>After setting this up and clicking <code>Ok</code>, you should be greeted with a smaller table containing summarised variables:</p>
<p><a href="https://i.sstatic.net/kqDkl.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/kqDkl.png" alt="Summary table, showing counts per RefID" /></a></p>

CC BY-SA 4.0
2022-10-13T19:02:20.487Z
I have purposely added a blank space after each block so that I may avoid errors for all the sums made.
Aranyak Goswami
CC BY-SA 4.0
2022-10-13T23:22:00.993Z
It may be that the pivot table will work with gaps, but bear in mind that leaving gaps in general makes bulk analysis more difficult. It can still be done, it just means that the data needs to be pre-cleaned, or alternatively analysed. I've added this clarification to my answer.
gringer
CC BY-SA 4.0
2022-10-12T11:12:19.177Z
2022-10-13T23:22:56.200Z
2022-10-13T23:22:56.200Z
gringer
<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>

CC BY-SA 4.0
2022-10-12T08:55:09.260Z
[Please don't do bioinformatics in excel](https://www.nature.com/articles/d41586-021-02211-4).
terdon
CC BY-SA 4.0
2022-10-12T10:42:16.733Z
Thanks for including the input here, but it doesn't look like what I'd expect to see in a well-formatted Excel spreadsheet. Are those `X)` really in the file? Why are there no counts for tRNA-Ala-AGC-3? Does your input table have empty lines?
gringer
<sequence-analysis>
Summing up data effectively in excel
# Summing up data effectively in excel

(Asked by: username_0 on 2022-10-12)

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.

**Tags:** sequence-analysis
> **Comments:**
> **username_1**: [Please don't do bioinformatics in excel](https://www.nature.com/articles/d41586-021-02211-4).  
> **username_2**: Thanks for including the input here, but it doesn't look like what I'd expect to see in a well-formatted Excel spreadsheet. Are those `X)` really in the file? Why are there no counts for tRNA-Ala-AGC-3? Does your input table have empty lines?  

---

## 2 answers

### Answer by username_3 on 2022-10-12. Score: 1

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.

> **Comments:**
> **username_0**: I have already done that but since there are so many cells I have to do the sums as you suggested for each subgroup individually and it takes several hours to do such sums, hence I am looking for a quick work around.  
> **username_3**: Hi @AranyakGoswami what you are up against is a RAM bottleneck. Excel shouldn't have trouble with the calculation on a standard desktop.  
> **username_2**: I suspect @AranyakGoswami is saying that it takes several hours to select regions around the counts for each subgroup, and add the associated formula into the correct cell near the gene counts. At about 1 minute per gene, several hours could mean about 10,000 genes.  

---

### Answer by username_2 on 2022-10-12. Score: 0

It looks like what you want to do is a pivot table, which provides summary statistics for fields, grouped by specified categories. As a prerequisite, this requires an input dataset that is well formatted; more generally:

* Put each observation or sample in its own row; don't leave row gaps between samples or groups. Leaving gaps makes bulk analysis more difficult and time consuming. It can still be done, it just means that the data needs to be pre-cleaned, or alternatively analysed.
* Put all your variables in columns - the thing that vary between samples, like ‘strain’ or ‘DNA-concentration’
* Have column names be explanatory, but without spaces
* Do not combine multiple pieces of information in one cell

For more information, see this Data Carpentries Workshop on the recommended way to structure data in spreadsheets:

<https://datacarpentry.org/organization-genomics/01-tidiness/index.html#structuring-data-in-spreadsheets>

If the data is prepared in this way, then selecting the whole table and creating a pivot table should work. Unfortunately, the programs keep changing where in the menu that can be found. Currently in LibreOffice, it's `Data` -> `Pivot Table` -> `Insert or Edit...`, wheareas on my version of Excel on Mac OSX, it's found at `Data` -> `Summarise With Pivot Table`.

This will load up a visual interface that allows you to drag and drop fields to summarise. For this data, you'd probably want *RefID* in rows, and *sum(Count)* as the data field:

[![Pivot table customisation dialog, showing RefID in rows, and sum(Count) as the data field](https://i.sstatic.net/SrROh.png)](https://i.sstatic.net/SrROh.png)

After setting this up and clicking `Ok`, you should be greeted with a smaller table containing summarised variables:

[![Summary table, showing counts per RefID](https://i.sstatic.net/kqDkl.png)](https://i.sstatic.net/kqDkl.png)

> **Comments:**
> **username_0**: I have purposely added a blank space after each block so that I may avoid errors for all the sums made.  
> **username_2**: It may be that the pivot table will work with gaps, but bear in mind that leaving gaps in general makes bulk analysis more difficult. It can still be done, it just means that the data needs to be pre-cleaned, or alternatively analysed. I've added this clarification to my answer.  

---

