Calculating Values Per User Between Dates

DanF

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

Paul Newcome

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.

DanF

This is awesome! 

Massive, massive thanks. 

Paul Newcome

Happy to help!