Function for a date range combined with a SUMIF

Question

Level 1

23 points

Function for a date range combined with a SUMIF

I need help with a Function,

I have 1 table with all my transactions.
I want another table that can sum up total amounts, for specific categories, by date.

Currently I can (with the SUMIF) function get Numbers.app to look up the "Category" list of different stuff / compare it with a specific "category" / and get the total "amount"

How do I add into or onto that, to get a result for ONLY a single month? Only the amounts in Jan, March, April, etc.?

SUMIF(2017::Category,Budget::'PERSONAL EXPENSES - PER

# Personal Expenses, non Business or non-expensABLE'::A4,Amount)

MacBook Pro (Retina, 15-inch, Late 2013), macOS Sierra (10.12.1)

Posted on Apr 23, 2018 11:37 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

31,002 points

Apr 23, 2018 3:56 PM in response to jameskachan

If you have multiple criteria, such as category and date(s), use SUMIFS. An example of a test for an entire month would be:

=SUMIFS(Amount, Date, ">="&DATE(2018,4,1), Date, "<="&EOMONTH(DATE(2018,4,1),0))

You could hard code in the first as ">=4/1/2018" if you know Numbers will always be set to MM/DD/YYYY format (not DD/MM/YYYY). You can hard code the second one, too, if you are sure you know the last date of the particular month. Or you can do this:

Assume some cell, which I will call B2 contains the number 4 (for April) and B3 = 2018 (the year).

=SUMIFS(Amount, Date, ">="&DATE(B3,B2,1), Date, "<="&EOMONTH(DATE(B3,B2,1),0))

Then you can simply change B2 and B3 to pick different months and years.

Reply

Answer 2

Badunit

Level 7

31,002 points

Apr 23, 2018 4:55 PM in response to jameskachan

Stupid me didn't notice your first column was "Month", which makes it easier than how things usually are. Usually there is only a date.

I do not use header cells as references, I use the standard alphanumeric cell references, but the example below maybe does what you want? Because you already have the month in a column, no need to bother with the date column.

Reply

Answer 3

Badunit

Level 7

31,002 points

Apr 23, 2018 5:32 PM in response to jameskachan

I don't see a comma between 2017:A and 12. That would cause an error. I am also wondering why

1) The reference to Category in your formula is not 2017:Category. Maybe it is okay, I find it strange)

2) Why 2017:A isn't 2017:Month Number. Maybe it, too, is okay, I just find it strange and

3) Why the cell containing "Alcohol and Bars" is highlighted because I don't see that cell referenced in the formula. This is seriously strange.

Also, I would replace the quoted text "Alcohol & Bars" with a reference to cell B2

Reply

Answer 4

jameskachan Author

Level 1

23 points

Apr 23, 2018 5:32 PM in response to Badunit

YES! Woohoo!
Thank you so much. That works.

That is great!

SUMIFS(2017::AMOUNT,CATEGORY,$A2,2017::A,12)

Reply

Answer 5

jameskachan Author

Level 1

23 points

Apr 23, 2018 4:41 PM in response to Badunit

Thanks, though I couldn't get that to work in combination with what I have. Sorry, I'm not great with functions.

I want to take a list that has a year's worth of transactions, and extract the Amounts of a number of Categories, limited to each month (Dec, Nov, Oct, etc).

I made a simpler spreadsheet example here.
In the monthly Total Tables (to the right), what would the SUMIFS function be?

Thank you again for your help.

DOWNLOAD of spreadsheet example file is here:
https://we.tl/aNbDMaj6hR

Reply

Answer 6

jameskachan Author

Level 1

23 points

Apr 23, 2018 5:38 PM in response to Badunit

Bonus question, how do you write out in the function (or point and click) to skip the header References and use those standard alphanumeric cell references D, C, A, ? Every time I tried, I just get the Header text. I like how it's cleaner this way.

Reply