SUMIF with a Date Range

Jessb9187

I am trying to calculate a total number of accounts that are based upon a few different criteria specifically within a date range. So for example, I need to calculate the total revenue for a list of clients that had canceled within the year 2018. I have a formula that I used to calculate this total for clients after 12/31/2018 but changing a greater than symbol to a less than symbol will pull all clients beneath 12/31/2017 and I only need for the year 2018. 

Here's an example of the formula I used which correctly identifies all 2019 criteria:

=SUMIFS({Client Tracker Onboarding V1 Range 4}, {Client Tracker Onboarding V1 Range 3}, "Cancelled", {Client Tracker Onboarding V1 Range 5}, "SMB - LOW", {Client Tracker Onboarding V1 Range 6}, >DATE(2018, 12, 31))

How do I get the range to show the exact same but within the dates 12/31/2017 to 12/31/2018?

Paul Newcome

There are a few ways to do this. The most straight forward would be to reference the same range twice and specify the two different sets of criteria.

 

=SUMIFS(.............., {Date Range}, > DATE(2017, 12, 31), {Date Range}, <= DATE(2018, 12, 31), ..............)

 

another option would be to reference the range once and use an AND function along with @cell references to combine the criteria.

 

=SUMIFS(.................., {Date Range}, AND(@cell > DATE(2017, 12, 31), @cell <= DATE(2018, 12, 31)), ............)

 

You can also reference the year directly using an @cell reference to avoid having to enter two specific dates.

 

=SUMIFS(....................., {Date Range}, YEAR(@cell) = 2018, .............)

 

There are a number of other ways that this can be tweaked to ensure further accuracy, consistency, and ease of use combining cell references and IFERROR statements. If you are interested in these other tweaks, let me know, and I will be happy to show and explain their uses.

Jessb9187

Yay! The second one works! Thank you for your help!

Paul Newcome

Happy to help! 

Sarah_lee123

Hello,

@Paul Newcome

I am responding to this post because it is relevant to the issue I am having. I am trying to add up the amount of clones cut off of a specific plant cultivar during a specific date range. Even better would be to use the week number rather than cut date, but either way I just need a number to pop out in the best possible way.

This one works, but does not have the additional criteria of matching a cultivar description.

=SUMIF({cut date}, AND(@cell >= DATE(2022, 6, 22), @cell <= DATE(2022, 6, 28)), {plug})

When I add the cultivar description match at the beginning of the formula, it gives an error message "INCORRECT ARGUMENT"

=SUMIF({culti desc}, [Cultivar Name]@row, {cut date}, AND(@cell >= DATE(2022, 6, 22), @cell <= DATE(2022, 6, 28)), {plug})

When I set it up like this I get the error message "UNPARSEABLE"

=SUMIFS({plug}, {culti desc}, [Cultivar Name]@row, {cut date}, >= DATE(2022, 6, 22), {cut date}, <= DATE(2022, 6, 28)))

Can you help me orient my equation to be able to pull this number? I will need to do this for every week of the year to capture cut rates over time.

Thanks so much in advance!!!

Genevieve P.

Hi @Sarah_lee123

As soon as you have more than one range that you're looking in to for criteria, you'll need to use SUMIFS (the plural version).

=SUMIFS({column to SUM}, {First Column with Criteria}, "Criteria 1", {Second Column with Criteria}, "Criteria 2")

This means your last option should work... except you have one extra closing parentheses at the end. Try removing it so you only have two ))

=SUMIFS({plug}, {culti desc}, [Cultivar Name]@row, {cut date}, >= DATE(2022, 6, 22), {cut date}, <= DATE(2022, 6, 28))

As a final note - if you're looking to capture this over time, what I would personally do is set up two date columns in your metric sheet, one with the week Start date and one with the End date, then reference these cells in the formula:

=SUMIFS({plug}, {culti desc}, [Cultivar Name]@row, {cut date}, >= [Start Date]@row, {cut date}, <= [End Date]@row)

That way you can change the dates in the cells instead of needing to edit the formula each week.

Cheers!

Genevieve

Sarah_lee123

Ok great, that worked. But now, I am wondering if we can do this using WEEKNUMBER rather than DATE. When I use the same formula, but swap out date for WEEKNUMBER, I get an error message "INVALID DATA TYPE"

New formula - kept DATE but changed criteria to WEEKNUMBER

=SUMIFS({plug}, {culti desc}, [Cultivar Name]@row, {cut date}, >=WEEKNUMBER(40), {cut date}, <=WEEKNUMBER(41))

New formula - changed range and criteria to WEEKNUMBER

=SUMIFS({plug}, {culti desc}, [Cultivar Name]@row, {WEEK NUMBER}, >=WEEKNUMBER(40), {WEEK NUMBER}, <=WEEKNUMBER(42))

Let me know if you want to see screenshots.

Thx!

Genevieve P.

Hi @Sarah_lee123

Yes we can use WeekNumber, however the WEEKNUMBER function in Smartsheet calculates based on a Monday-to-Sunday week.

This means that if you're looking for a range from "week number 40 to week number 41", you'll see all dates from Monday the 3rd of October through to Sunday the 16th of October:

Is that what you would like? If so, then the way you would use WEEKNUMBER is by comparing the week number of the cell to the number you want:

=SUMIFS({plug}, {culti desc}, [Cultivar Name]@row, {cut date}, WEEKNUMBER(@cell) >= 40, {cut date}, WEEKNUMBER(@cell) <= 41)

Cheers,

Genevieve