How to calculate the sum of every row that is in the same week as the current row in Excel 2010?

Question

I have an Excel table with data about working hours. I want to add a column that calculates the total hours in this week. For example: if I worked 3 days in a single week, the formula has to find every other row that is in the same week and sum the hours worked.

Each table row looks like this:

Date, From (time), To (time), Break (time), Today total (time)

I am thinking of a formula like this: Check the date in the current row and get the calendar week. Now loop through every row and check if the calendar week is equal to the one in the current row. If it is the same, then add the "Today total (time)" to the weekly sum.

Unfortunately I can't find a for each command. Something like =(FOR(A2:A10)(IF....). It would be useful to have something like local variables. I found out that there might be a possibility to write more complex formulas in VBA, but I'm not sure if it is possible to calculate a specific cell value with VBA or it is used for something else.

Answer 1

The formula you are looking for is called SUMIFS

I assume you want to total the today totals (E), in which case the formula would look like this. If your data extends farther than row 10, you'll have to use a higher number in those cell references:

=SUMIFS($E$2:$E$10,$A$2:$A$10,">"&A2-weekday(A2),$A$2:$A$10,"<="&A2-weekday(A2)+7)

Here's a breakdown of how it works. SUMIFS takes a sum field, then pairs of criteria range and criteria. So the sum range is:

$E$2:$E$10

the first criteria range is:

$A$2:$A$10

And the first criterion is:

">"&A2-weekday(A2)

This criterion is building a greater-than statement by concatenating > with a date that corresponds to the Saturday before the date on the current line. WEEKDAY gives a number for the day of the week (for example, Friday is 6). So today's date minus the weekday always gives the Saturday before.

the second criteria range is the same as the first, but the criterion is built differently:

"<="&A2-weekday(A2)+7

The +7 is there to give this Saturday, a week after next Saturday. So in summary we are comparing the dates in column A to a range greater than last Saturday and less than or equal to this Saturday, i.e. this week.