Numbers SUMIF by dates within current week

numbers

I have a table like this:

First Column Dates, Last Column Amount Made that Day

Which basically contains jobs I've done, the dates I did those jobs and how much that job made me.

I'd like to find out how much money I've made this week (current date is 09/10/2015), with Monday being the first day of each week and Sunday the last. Monday of this week was on 05/10/2015.

circled week so far

Above you can see the values I'd like to sum, because they were all jobs completed within this week.

I can't get anything closer than this:

=SUMIF(Dates, TODAY(), Earnings)

which only sums the money I've made today, but I want the whole week; how can I sum all the money I've made in this week?

Best Answer

Of course there may be other ways of doing this, but here is one.

To accomplish this dynamically (so that it changes when the current week changes), one can use the WEEKNUM( ) function.

  • Create a column (you can hide this later if you don't want it to show. See edit below) with the calculation of:

WEEKNUM( A1 ) -- Where A1 is the first cell with a date.

  • Drag this formula down the column.

Now you can use this formula in a cell to get the sum for the current week.

SUMIF(Table 1::B,WEEKNUM(TODAY()),Table 1::C)

This should give you the desired result.

See the screen shot. The Function Inspector pane has more information on these functions.

Numbers Formula

EDIT

To hide a column: - Highlight the column - Click the disclosure arrow (or control click / right click) - Select Hide Column

To show again, right click in an adjacent column letter and select Unhide.

Hide column dialog

Related Question