Welcome!

SUMIF with a Date Range

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?

Find more posts tagged with

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Not enough space in the cell for my notes
Hi, I'm using smartsheet to track updates on each task in a tracker. These updates may span over a year, so they get lengthy. I was excited about smartsheets because viewing these notes is a lot cleaner. However, I've just realized that there is a content limit to a cell, which is a huge issue. While excel was annoying to…
how can i overlap 2 tasks.
Hello, i've a question about 2 tasks. Task 1 has for example 4 hours and should be on day 1 for example 6 january. Task 2 has 30 hours and should be on start on 4 january and futher. How can i manage that on 6 january task 2 has only 4 hours? Now i got the message user is over-allocated because task 2 is planned on a full…
Update Automations
Hi All, We have several update automations on various solutions - and have worked very well for getting info from people that dont have smartsheet access - how ever in Sept we switched over to SSO and MFA and activated the Safe Share - We have now run into an issue that when our update automations trigger and people within…