Auto Summation? Closed

Question

Hello, 
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.
                             



Configuration: Windows 7 / Internet Explorer 8.0

IneptMacro · Answer

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 http://wikisend.com/ . 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?

Sam · Answer

Ah didn't think about uploading  an example via a link thanks.

http://wikisend.com/download/468052/Sauter Mean Diameters.xlsx
[URL=http://wikisend.com/download/468052/Sauter 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 · Answer

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?

Sam · Answer

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

JayHerring · Answer

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...


D32	
20	0
30	0
50	0
(empty)	2032
D32	
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

Auto Summation?

5 responses

Subscribe To Our Newsletter!