Calculating Values Per User Between Dates
Hi Guys,
I'm probably missing something really simple but I'm struggling to get my formula to work. I have a metrics sheet that is using cross-sheet referencing to pull data from a separate sheet.
Operators add values to the sheet and I've used the automated created date function to keep track of when the values were added. I need to calculate what the total value is per month, per operator. So I'm using the following:
=SUMIFS({TotalValue}, {Name}, [OperatorName]16, {CreatedDate}, >=DATE(2019, 6, 1) < DATE(2019, 7, 1))
However, the calculation is calculating everything added to the sheet prior to July 1st but I just need it to calculate everything that was created in June. If someone could point me in the right direction and let me know where I'm going wrong that'd be great.
Thanks
Dan
Comments
-
There are a few different ways to tackle this. I will give the two easiest (in my opinion) examples, both of which leverage the AND function.
Also note: For the best results when referencing the Created or Modified system columns, a DATEONLY function is helpful.
When using the same range for two sets of criteria within a COUNTIFS (or SUMIFS for that matter), you have two options.
1. COUNTIFS(..............., {Range}, Criteria 1, {Range}, Criteria 2)
or what I will be using in my examples
2. COUNTIFS(..........., {Range}, AND(Criteria 1, Criteria 2))
.
Modifying your original formula is as simple as adding the AND function with @cell references (I will also be adding in the DATEONLY function)
=SUMIFS({TotalValue}, {Name}, [OperatorName]16, {CreatedDate}, AND(DATEONLY(@cell) >= DATE(2019, 6, 1), DATEONLY(@cell) < DATE(2019, 7, 1))
.
Another option would be to reference the YEAR and MONTH of each date.
=SUMIFS({TotalValue}, {Name}, [OperatorName]16, {CreatedDate}, AND(YEAR(DATEONLY(@cell)) = 2019, MONTH(DATEONLY(@cell)) = 6))
.
Using cell references in place of specific dates and/or numbers is also helpful because it allows you to avoid editing the formula directly and risk breaking something.
-
This is awesome!
Massive, massive thanks.
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!