Macro to sum values in one column based on data in another colum while ignoring blanks

Hi Everyone,

Being somewhat new to VBA, I am having great difficulty wrapping my head around my latest problem. I have attached a simplified version of the project I am working on.

The sheet named "Times" contains 3 columns of importance, titled "Stage", "Time A", "Time B". I want to use a macro to sum all of the values in "Time A" column that are associated with each specific stage number. Also, using a macro, sum all of the values in "Time B" column that are associated with each specific stage number. The macro would also have to create the chart on the "Summary" sheet.
The end result would have to look something like what is on the sheet named "Summary". The colour coding was used to help visualize which cells are related to each specific stage number. For example, all Time A and Time B cells associated with Stage 10 are coloured gold. As can be seen, there are blank cells in many of the Time columns, as well as in the Stage column. Also, Stage 10 and Stage 30 have additional entries near the bottom of the table. On the "Summary" sheet, all the "Time A" values would be summed (2.20 + 4.50 + 6.00 + 0.25 + 0.50 = 13.45), as would the "Time B" values (4.0 + 0.5 + 0.5 = 5.0)
Colour coding is not required on the working charts.

Note:
The chart on the "Times" sheet is dynamic (although not shown), such that the number of stages and associated Times can vary, by inserting or deleting rows of the chart.

I look forward to your responses.
Thanks for you help!!

Thanks oeldere for you response. I'm not familiar with pivot tables, so I may be missing something, but I do not believe this is summing the values in the manner that I need.

Another option

Please Login or Register  to view this content.

Thanks AB33 for your contribution. The code you provided essentially compressed the table, removing blank cells and shifting up, but does not associate "Stage" number with associated times. Perhaps my original post was not clear. I will try to explain it better by replying to the thread.

Thanks.

I appreciate the responses I have received so far, but none seem to be producing the results I'm looking for. Oeldere came very close, but that specific method overwrites the "Notes" cells in the "Times" table. I believe my original post was not very clear. I will attempt to explain better:

Looking at the table on the "Times" sheet...Looking specifically at the "Stage" column...you can see 2 occurences of cells containing "10". I'll refer to these as "Stage 10". You can see that all the cells (in Time A, Time B columns) associated with Stage 10 are coloured gold (just for visulaization). So, for each occurence of "10" in the Stage column, sum the times in Time A column until a different Stage number is encountered in the Stage column (or no more Stage numbers are encountered).
Then perform the same routine to sum the associated times in Time B column.

Then, of course, create the table on the "Summary" sheet. I would consider this secondary, as I have been smaking some progress on this particular task.

Thanks again everyone!

Thanks oeldere. I'm not sure that your second file is wrong. However, I notice that it fills in the "Notes" column. If I have notes in tat column, will they be over-written? I think what you have provided will work, but I will have to learn pivot tables so I can understand.
The final chart that your solution creates is exactly the result I need. I just have to know that the "Notes" column will retain any notes that I have in there before executing code.

That said, I'm not sure I can use a pivot table. I need to be able to apply some method to achieve the result to files that already exist (about 2500 files). If the process was all in VBA, then I could export VBA to each file, then execute it, without creating a pivot table in each file.

I am relatively new to VBA, so I may not be able to articulate what I'm asking for as well as may be needed.
Thanks again for your help.

Thanks. I will have to spend some time learning pivot tables before I comment any further on your method.

Thank you oeldere.

I'm still stuck on this one. I do not believe a pivot table will work for my particular needs, as I would have to create the pivot table on each spreadsheet. I have a large number of files that I need to apply the solution to, so I want to be able to perform from a macro.
Anyone else have any ideas? If my issue has not been explained clearly, please llet me know so I can clarify.

Thanks everyone.

Thanks for your continuing help oeldere. I'll try my best to explain better.
I will refer to the file attached to my original post. For now, assume that the sheet named "Summary" does not exist (will get to that later). I have a large number of files that do contain the sheet named "Times". However, each one is unique. Some may have stages 5 to 30, others may have stages 5 to 160 (but column A is always "Stages", column B is always "Notes", column C is always "Time A" and column D is always "Time B").
Each individual Stage number (5 to 50 in this example) has times associated to it. For example, Stage 10 appears twice, and has these times associated to it: (Time A) 2.20, 4.50, 6.00, 0.25, 0.50. and also (Time B) 4.0, 0.5, 0.5. The macro would identify all Time A entries that are associated with Stage 10, and sum them. The macro would also identify all Time B entries that are associated with Stage 10, and sum them. The macro would do the same for all stage numbers. Then, create the sheet called "Summary", create the chart (as shown), and populate it with the Time A sum and Time B sum for each Stage number.

Note about the "Times" sheet: Stage numbers can appear any number of times. Corresponding times can be listed in the same row (in Time A and Time B columns), but also in the rows below, until another Stage number is encountered.

Remember, I have a great many files that I want to apply such a macro to, and the chart on the "Times" sheet varies in length according to the raw data collected for each file/project.

I certainly hope this explaination was better.
Thanks again.