Sum of multiple tables data

[meetvivek72]

well, all i need is the sum of all values of nucleotides A G T C & N at the end of the table,


Actually i have a huge size file(cant attach here) which includes many tables may be in thousands.
Each table has three fields i.e. nucleotide, Number & Mol. The values are also given against each nucleotide as u can see it from table. All i need is the sum of the values from Number column for A G C T & N, it would be something like this :
I am displaying here only for A , but it should show the sum for others(GCTN) too

Nucleotide Number Mol
A 7727 (3044+1652+1651+1380)

i need only the sum 7727 in Number field not (3044+1652+1651+1380)etc.....

[JBeaucaire]

You'll need to draw out the data into adjacent columns to do simple SUMIF.

B1: =IF(AND(LEFT(A1)=" ", OR(LEFT(TRIM(A1))={"A","C","G","T","N"})), TRIM(A1), "")
C1: =IF($B1="", "", LEFT($B1))
D1: =IF($B1="", "", MID($B1,3, FIND(" ", $B1, 3)-3)+0)

Copy those three cells down the whole dataset and the key cells will extract.

Now create a table in F:G, put the letters A C G T N in F1:F5, then put this collection formula in G1 and copy down:

=SUMIF(C:C, F1,D:D )


You can drop any data you want into column A, then make sure the B:D formulas cover that data range and the table in F:G will be correct.