VBA Code to sum values in one column based on the value in another column in excel

Question

I am a VBA Newbie and a first timer on this forum - I have followed all the instructions on the "how to ask" page - I may have missed something please do advise

I am creating an EV report for project tracking based on the following column headers (showing only a few for brevity)

"TaskName" "Status" "BaselineStart" "BaselineFinish" "BaselineEffort"

need to sum up the values in the BaselineEffort column in 7 day increments after checking if the value in the BaselineFinish column is less than or equal to the 7th day value

I believe the answer lies in using arrays, but need handholding with that concept to understand how it works

Pivot and Excel formulas dont work for me because the table is dynamic while the report is static and I need to remove user intervention in creating the report

Thanks in advance

Do you know the column-number in which you want to perform this operation? In that case, it could just be a simple For Loop. — Anirudh Ramanathan, Nov 10 '12 at 21:16
Just to clarify, as no data were provided it is hard to tell what exactly do you need, you have some dates and data for each date. What you want is to create a sum of the data for each week based on the dates? — MPękalski, Feb 01 '13 at 16:04

user2200765 · Answer 1 · 2013-03-22T21:51:44.020

say that the criteria column starts in A1 and the colume to be summed starts in B1

do while not isempty(range("a1").offset(x,0))
  if range("a1").offset(x,0) = SomeValue then
    sum = sum + range("a1").offset(x,0)
  end if
  x = x + 1
loop

this code will run until it has looked at each item in column A, and add the value in column B to a sum I called "sum" if the value in column A equals "SomeValue." I doubt you can actually use the variable name "sum."

I hope that's useful.

VBA Code to sum values in one column based on the value in another column in excel

1 Answers1

Linked