SUMIF with date range and dropdown list categories
Hi everyone,
So I have columns Type (dropdown list, 3 values, Tools, Chemicals, Clothing), Date and Amount ($).
I am trying to come up with a formula to sum amount of $ spent in a certain month in each Type of expense. So, for example, for much was spent in type Clothing between 06/01/18 and 06/30/18.
Is there a way to do it? We have other sheets regarding our budget where there are as many as 4-6 values for dropdown Type column.
Please help
Comments
-
Assumption: 'between 06/01/18 and 06/30/18' implies Month = June, Year = 2018 and you aren't (yet) looking for a generic 'enter two dates - determine sum for them' (can be done, not so simple)
Assumption: this formula is not in the [Type], [Date], or [Amount ($)] columns.
Basics:
=SUMIFS([Amount ($)]:[Amount ($)], Type:Type, "Tools", Date:Date, AND(MONTH(@cell) = 6, YEAR(@cell) = 2018))
That will sum the amounts where the Type is "Tools" and the Date is June 2018 and it will do so over the entire column.
You could copy that somewhere and then manually change the criteria. I don't recommend it. (3 choices x 12 months x Y years ...)
Advanced:
Ultimately, I would transfer the formulas to another Sheet and reference them using X-Sheet References.
https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets
You can replace "Tools", 6, and 2018 with references to cells that hold the values.
I hope this should get you started
Craig
-
A few variations...
If you have a simple table set up where each dropdown selection is listed only once (possibly at the top of the page as a summary or on another sheet using x-sheet references), you could use
=SUMIFS([Amount ($)]:[Amount ($)], Type:Type, Type@row, Date:Date, AND(MONTH(@cell) = 6, YEAR(@cell) = 2018))
That will give you the total for whatever is in the Type column for the row your formula is in.
This will give you a running total of the current month:
=SUMIFS([Amount ($)]:[Amount ($)], Type:Type, "Tools", Date:Date, AND(MONTH(@cell) = TODAY(), YEAR(@cell) = TODAY()))
-
Thank you Craig! Will be experimenting
-
Thank you!
-
It worked, thank you so much!!
We'll try referencing afterwards too
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives