Date Range Help (SUMIFS)

Question

JMM_5490 Author

Level 1

9 points

Date Range Help (SUMIFS)

Hi,

I've got a table in which I input raw day to day data from the jobs I do (date, total earnt that day).

I'm now trying to set up another table that will show me my monthly earnings.

I've tried the following formula's:

=SUMIFS((Raw Data::Total), (2012-2013 Date), (7th December 2012)

This correctly displays the figures I want, but is limited to just one day.

=SUMIFS((Raw Data::Total), (2012-2013 Date), (December)

This doesn't bring up the whole month and instead brings up a figure of 0.

=SUMIFS((Raw Data::Total), (2012-2013 Date), >=1st December 2012, (2012-2013 Date), <=31st December 2012)

This brings up a syntax error

How do I correctly add a date range? (I'd prefer to be able to just specify the month rather than a beginning and end date if possible?)

(Also I'm using the latest Numbers App on an iPad 2).

iPad 2, iOS 6

Posted on Dec 12, 2012 1:33 PM

Reply

Answer 1

Badunit

Level 7

30,708 points

Dec 12, 2012 4:07 PM in response to JMM_5490

The conditions to use for the SUMIFS are ">="&DATE(2012,12,1) and "<="&EOMONTH(DATE(2012,12,1),0)

You could use ">=12/1/2012" and "<=12/31/2012" or "<="&EOMONTH("12/1/2012",0) but using the date function is more robust and works regardless of the international settings.

If you want to use SUMIF and specify only the month, you'll need a column for the month of each of the dates, using the formula =MONTH(...) . Then your condition in the SUMIF will be "=12" and you'll look in the "month" column. You can then hide the "month" column.

Reply

Answer 2

JMM_5490 Author

Level 1

9 points

Dec 13, 2012 1:30 PM in response to Badunit

Thanks very much. I'd spent ages trying to work it out, but wasn't getting anywhere. Glad I've finally been able to sort it out :-)

Reply

Answer 3

Barry

Level 9

60,023 points

Dec 15, 2012 10:20 PM in response to Badunit

One caution on Badunit's second suggestion: As written, the SUMIF tests only for the month, and will include amounts occurring in that month in any year, if more than one year is included in the table.

Not an issue if your table includes data from only a single year, but if it extends over more than a single year, you might want to revise the formulas to allow for that. If so, post a reply requesting that revision.

Regards,

Barry

Reply