Excel (VBA): Going through rows for specific date range

Question

First of all: I'm just an average Excel user (so not really experienced with formulas and I just heard of VBA as a possible solution).

I have a table with dates (can be multiple ones for each day) and Values for these dates. I now want to get the SUM of all dates of each week (e.g. 8th May-14th May are calendar week 19, so all values belonging to that week should be added to the SUM value of week 19 [red box]). This also should be a one-time setup, so that I can just apply that for all following weeks.

Is there a possible way to solve that with just Excel formulas or do I need VBA (some formulas or/and code would be great!)

Matt · Accepted Answer · 2017-06-30 11:05:59Z

3

You can try formula - weeknum to get values in your "week" column, then use sumif to get values.

It will be like below;

Make a column called Week-Number: =WEEKNUM("Date Column", 2) The number 2 assumes that Monday is the first day of the week.

And then apply Remove Duplicates on that column, and get the Week column.

For column called value use this formula: =SUMIF("Week-Number Column","="&"Week Column (each row)", "Value Column")

Look at the pictures below; First one shows the answers and the second one shows the formulas:

Values

Formulas

edited Jun 30, 2017 at 11:05

Matt

75.4k26 gold badges156 silver badges181 bronze badges

answered May 17, 2017 at 13:03

Zedee.Chen

2121 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

OSX55 Over a year ago

Thank you! I couldn't quiet figure out how to go trough every row until the date gives back another week 'number'. If I want to to it for only one row it's: =IF(WEEKNUM(A1; 21)=D1; WEEKNUM(A1)) But it needs to not only go this one row…

Zedee.Chen Over a year ago

refer to mrsundquist's answer, what you need to do is select row C, and Click menu : data - filter - advanced, and retrieve the distinct values to row E

HaveSpacesuit · Accepted Answer · 2017-05-17 15:47:03Z

1

You'll need to add another column to show the week number. Then it is easily done with SUMIF.

The formula in C2 is =WEEKNUM(A2,2). This starts the week on Monday, as your highlighting above shows. (The week numbers are different than what you show. If you want, you can add a -1 at the end.)

The formula in F2 is =SUMIF(C$2:C$24,E2,B$2:B$24), then drag it down for future weeks.

answered May 17, 2017 at 15:47

HaveSpacesuit

4,0727 gold badges50 silver badges69 bronze badges

Collectives™ on Stack Overflow

Excel (VBA): Going through rows for specific date range

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related