Auto Summation?

Sam - Aug 20, 2010 at 01:42 PM
JayHerring Posts 6 Registration date Friday August 20, 2010 Status Member Last seen September 8, 2010 - Aug 20, 2010 at 03:58 PM
I need help in a macro I'm working on. Right now I have different sets of data arranged in the following manner as an example

D32 Colum1 Colum2
20 10.5 20.2
5 7.2
2.2 3.5
. .
. .
D32 Colum1 Colum2
30 11 22.5
15 21
10 2.4
D32 Colum1 Colum2
45 1 0.2
9 7.2
2.2 3.5 ...etc

Now I'd like to add up the sums but one set of data might be made up of 5 rows whereas the next set of data is made up 7 and is always different.
Is there a way to create a macro that automatically know where the end of the data is?
I'm a beginner in using macros here so thanks in advance.


5 responses

Hi, in this case it doesn't seem like a macro is what is needed. however the best way to describe your problem is to post an example of it to a file share site like . Once it is uploaded, post the link to it on here and you'll probably have an answer in no time.

Also, a brief recap of the issue with references to the cells in the example would be helpful... like which sum you are trying to do, the whole grid or the row/column?
Ah didn't think about uploading an example via a link thanks. Mean Diameters.xlsx
[URL= Mean Diameters.xlsx]Sauter Mean Diameters.xlsx[/URL]

So if you look into the file you'll see 3 columns labeled "D32" "Data1" and "Data2" respectively. I'm trying to get the sum of "Data2" but if you look down to the next set of results you'll see that it has more rows than before so they're never exactly the same. I'd upload the original file unfortunately I don't have it on me at the moment (getting my daily dose of caffeine :-) )

Reason why I'm doing this? Well, long story short is the original data are part of a set of histograms that have been exported from a laser system that measure droplet sizes in sprays(hence D32 referring to sauter mean diameter). The raw data was exported as a .csv and I'm trying to get some statistical info on the data(deviation, error, confidence interval...etc).
JayHerring Posts 6 Registration date Friday August 20, 2010 Status Member Last seen September 8, 2010
Aug 20, 2010 at 03:25 PM
It looks as though each set of data is seperated by a "D32" and you are asking how to sum the "data2" column for each seperated set. Then perhaps summing the product of all three?

If this is what you need, simply select the cell "C23" and type "=sum(" at this point select all of the numbers you want to sum (the ones above c23 and before the Data2" title) and then type a close bracket.

Repeat for each data set, once this is done you can see each summed total for each D32 data set. Then select an empty cell, let's say e2 and then type (in your example) "=sum(c23,c50,c82)" - this will give you the sumtotal.

Have i answered you correctly or is my caffeine level in need of a top-up?
Nope I'm afraid not, looks like you are in need of a caffeine top-up. You see, would do that but the actual file is very very long. The total number of rows is as much as 50,000 and to make matters even worse, I have another 30 or so files similar to this one. Doing the "=sum()" by hand is out of the question, which is why I resorted to creating a macro. Again the problem is I don't know how to tell my macro or excel to recognize where the end of each set of data is. Wait a second....A light bulb may have just flashed in my head. Gotta try something out and see if it work....Dashing back to the lab

Didn't find the answer you are looking for?

Ask a question
JayHerring Posts 6 Registration date Friday August 20, 2010 Status Member Last seen September 8, 2010
Aug 20, 2010 at 03:58 PM
I hope your home-grown solution works for you,
If it is possible for you to create a database where each D32 type is entered in each and every row, so the D32 "20" would be in every row all the way down until the "30" starts, this would allow you to extract the sum using a pivot table, as the data is though a pivot table would look like this...

20 0
30 0
50 0
(empty) 2032
Total Result 2032

because each number in data2 would have to be assigned to a D32 type, the pivot table will only recognise the sum as being in an "empty" column