1

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.

3

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.

3
  • The formula looks pretty good. I want to test it out but unfortunately I have a german version of excel, so I have to translate it. I'm not quite understanding the syntax of this part: ">"&A2-weekday(A2),$A$2:$A$10,"<="&A2-weekday(A2)+7) May 17 '19 at 14:41
  • I've added a breakdown of how the formula works.
    – Greg Viers
    May 17 '19 at 14:50
  • Thanks, works perfectly. May 17 '19 at 21:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.