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 crosssheet 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!