I am trying to use numbers for a budget, and would to keep a continuous listing of transactions. In my table, I would like to get the total funds used in a given category but only in the current month.
What formula would show the sum of a given category, but Only for the current month?
my transaction table has date, description, category, amount, and reconciled.
thanks!
Thank you!
I think this should work better.
C2=SUMIFS(Transactions-CAP ONE::Transactions::Amount, Transactions-CAP ONE::Transactions::Category, A2, Transactions-CAP ONE::Transactions::Date, ">="&DATE(YEAR(TODAY()), MONTH(TODAY()),1),Transactions-CAP ONE::Transactions::Date, "<="&EDATE(DATE(YEAR(TODAY()), MONTH(TODAY()),1),1)−"1d")
Thank you!
I think this should work better.
C2=SUMIFS(Transactions-CAP ONE::Transactions::Amount, Transactions-CAP ONE::Transactions::Category, A2, Transactions-CAP ONE::Transactions::Date, ">="&DATE(YEAR(TODAY()), MONTH(TODAY()),1),Transactions-CAP ONE::Transactions::Date, "<="&EDATE(DATE(YEAR(TODAY()), MONTH(TODAY()),1),1)−"1d")
I made up a scenario as an example... you may post back with a specific question about your situation after trying to adapt this solution:
Enter the date in the column A
Enter the amount in column B
I arbitrarily, and for no reason, selected E2 to contain this months total.
Select cell E2, then type the equal sign.
Then copy and paste the formula from here:
SUMIFS(B, A, ">="&DATE(YEAR(TODAY()), MONTH(TODAY()),1), A, "<="&EDATE(DATE(YEAR(TODAY()), MONTH(TODAY()),1),1)−"1d")
you can also just select the cell then copy and paste this that includes the equal:
=SUMIFS(B, A, ">="&DATE(YEAR(TODAY()), MONTH(TODAY()),1), A, "<="&EDATE(DATE(YEAR(TODAY()), MONTH(TODAY()),1),1)−"1d")
shorthand for this is:
E2=SUMIFS(B, A, ">="&DATE(YEAR(TODAY()), MONTH(TODAY()),1), A, "<="&EDATE(DATE(YEAR(TODAY()), MONTH(TODAY()),1),1)−"1d")
sumifs() has the following basic form:
SUMIFS(sum-values, test-values, condition, test-values…, condition…)
for your specific situation...
SUMIFS (sum-values, category, category condition, date, start date condition, date, stop date condition)
C2=SUMIFS(Transactions-CAP ONE::Transactions::Amount, A, A2, Transactions-CAP ONE::Transactions::Category,">="&DATE(YEAR(TODAY()), MONTH(TODAY()),1), A, "<="&EDATE(DATE(YEAR(TODAY()), MONTH(TODAY()),1),1)−"1d")
I may have a typo because I did not try to make the same tables and enable header names as labels (like you have). So
You are the man, Sir. Thank you so much for your help. That worked like a charm.
I think I messed up... I'm sorry.
Please post a screenshot of the table where the transactions are. If there is real, personal information in that table, please only show the header row where the names of the columns are. Please make sure cell A1 is selected (it's not super important which cell is selected. Please remember don't show your personal data.
Thanks for the help! Does this screen shot help to add context around the category aspect?
Still having an error...is there a place in the formula that the date column should be referenced? See below...that is the transactions tab.
I realized I messed up your formula a bit, but I'm still having that error...
No worries. I appreciate your help!
Numbers SumIfs? Category And this month
