SumIfs function with a date

Vanessa T.

Hello! I have a sumifs formula that works great:

=SUMIFS({Amount}, {Branch Number}, Label@row, {Request Type}, "Maintenance")

My problem is when I try to add a date aspect to this formula. I want to break this number down by month, so how much was spent at a specific branch by request type and month. I added this formula:

=SUMIFS({Amount}, {Branch Number}, Label@row, {Request Type}, "Maintenance", {Date} <= DATE(2024, 1, 31), {Date} <= DATE(2024, 1, 1))

and received an invalid operation. What am I doing wrong?

MarceHolzhauzen

Hi @Vanessa T.

It looks like you're missing some of the comma separators to your date conditions and you're using two <= conditions for the date range, but you need one condition for the start date and another for the end date. Instead of two <= comparisons, you should use one >= for the start date and one <= for the end date to define the range.

=SUMIFS({Amount}, {Branch Number}, Label@row, {Request Type}, "Maintenance", {Date}, >= DATE(2024, 1, 1), {Date}, <= DATE(2024, 1, 31))

This formula specifies that you want to sum the amounts where:

1. The branch number matches the current row's label (Label@row).
2. The request type is "Maintenance".
3. The date is greater than or equal to January 1, 2024 (>= DATE(2024, 1, 1)).
4. The date is less than or equal to January 31, 2024 (<= DATE(2024, 1, 31)).

If you're still getting an error, double-check that the {Date} column is correctly formatted as a date column, and ensure the curly braces ({}) match the correct column references for your sheet.

Hope this helps!

Paul Newcome

I'm just now seeing this. Haha.

But yes. Technically the only thing missing is that comma as well as the greater than/less than piece.

I personally would use a slightly different approach, but it is more personal preference and easier for me to manage. The first thing I would do is use an AND function anywhere I am repeating the same range. This just helps me keep all of my criteria for that range together, makes sure I don't type in the wrong range, etc..

=SUMIFS({Amount}, {Branch Number}, Label@row, {Request Type}, "Maintenance", {Date}, AND(@cell <= DATE(2024, 1, 31), @cell <= DATE(2024, 1, 1)))

The next thing I would do is specify the month and year when looking for a month instead of using a date range. It is easier for me to do it that way instead of trying to remember which months have 30 days, which have 31 days, and whether February is going to have 28 or 29 that particular year. Also makes for building out tables so I can use cell references and whatnot a little easier.

=SUMIFS({Amount}, {Branch Number}, Label@row, {Request Type}, "Maintenance", {Date}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024))

@MarceHolzhauzen Here's a formula I like to use to output a "text" for a month if that text is going to be the three characters.

=MID("12JanFebMarAprMayJunJulAugSepOctNovDec", MONTH(Date@row) * 3, 3)

Will Jeffords

@Vanessa T. it's really funny that some of us were troubleshooting a very similar issue in another venue earlier today @Darla Brown @David Tutwiler @Samuel Mueller @Kelly Drake and it may not be the most elegant formulaic answer (help us out with elegance if you have it @Paul Newcome), but our consensus was that these become easier if you create a helper column (like a checkbox) that separates out the date calc part of this (with a column formula that checks your date parameters) and then incorporate that checkbox into your SUMIFS…instead of trying to do the calc all in one formula. You can then hide the column or move it to an unobtrusive place in your sheet to keep things "cleaner".

Let me know if this makes sense as a work-around!

Best,

Will

MarceHolzhauzen

Hi @Vanessa T.

It looks like you're missing some of the comma separators to your date conditions and you're using two <= conditions for the date range, but you need one condition for the start date and another for the end date. Instead of two <= comparisons, you should use one >= for the start date and one <= for the end date to define the range.

=SUMIFS({Amount}, {Branch Number}, Label@row, {Request Type}, "Maintenance", {Date}, >= DATE(2024, 1, 1), {Date}, <= DATE(2024, 1, 31))

This formula specifies that you want to sum the amounts where:

1. The branch number matches the current row's label (Label@row).
2. The request type is "Maintenance".
3. The date is greater than or equal to January 1, 2024 (>= DATE(2024, 1, 1)).
4. The date is less than or equal to January 31, 2024 (<= DATE(2024, 1, 31)).

If you're still getting an error, double-check that the {Date} column is correctly formatted as a date column, and ensure the curly braces ({}) match the correct column references for your sheet.

Hope this helps!

Darla Brown

ooooo @MarceHolzhauzen coming in with another option and beating @Paul Newcome ! 🤩

@VanessaT please let us know if this worked!

MarceHolzhauzen

Oh snap! I didn't even see all of you Rockstars in this thread!

Would love to hear Paul's view on it

Vanessa T.

@MarceHolzhauzen, Thank you so much! That worked!! I apprecaite your help with this!!! @Will Jeffords That was an excellent suggestion, I can see where that option would have worked as well.

Have a great day!!

Will Jeffords

Yes, @MarceHolzhauzen nailed it! My approach is sometimes practical if you actually have to do a date calculation (versus the simple date checking that your case has). @Kelly Drake pointed this distinction out to me earlier and it's a great rubric for SUMIFS…if you are just checking a set of conditions (including dates), pile em on! If you have checks and calcs, maybe consider helper columns.

Learnings all around! Community Rocks!!🎸

MarceHolzhauzen

@Vanessa T. Glad to have helped out here.

Just posting an idea down here to (maybe it's useful, maybe it's not:
If you convert your {Date} column to a month and year in a helper column, You will be left with rows of data that have months and years attached to them. You can use this column to drive monthly metrics and calculate things in a metric sheet that could be posted in chronological order.

This, in turn, can be added to graphs that make sense.

Here's how:
1. Add a column to your sheet called reporting period.
2. In this column; add the following formula:
=YEAR(Date@row) + "_" + IF(MONTH(Date@row) = 1, "JAN", IF(MONTH(Date@row) = 2, "FEB", IF(MONTH(Date@row) = 3, "MAR", IF(MONTH(Date@row) = 4, "APR", IF(MONTH(Date@row) = 5, "MAY", IF(MONTH(Date@row) = 6, "JUN", IF(MONTH(Date@row) = 7, "JUL", IF(MONTH(Date@row) = 8, "AUG", IF(MONTH(Date@row) = 9, "SEP", IF(MONTH(Date@row) = 10, "OCT", IF(MONTH(Date@row) = 11, "NOV", IF(MONTH(Date@row) = 12, "DEC"))))))))))))

Formula Explained:

1. YEAR(Date@row):
   • This extracts the year from the date in the cell referred to as Date@row.
2. "_":
   • Adds an underscore (_) between the year and the month to separate them in the resulting text.
3. IF(MONTH(Date@row) = X, "XXX", ...):
   • This series of IF statements checks which month the date falls in using MONTH(Date@row) and returns the corresponding three-letter month abbreviation (e.g., "JAN" for January, "FEB" for February, etc.).
   • For example, if the date is in January, it returns "JAN", if it’s in February, it returns "FEB", and so on.

Result:

The formula outputs a string in the format of Year_MonthAbbreviation.

Example:

• If Date@row is 2024-10-16, the output would be:
  • 2024_OCT

Now, plug this simplified column back into your sheet and you can actually see exactly what's going on.
You can also use this column to count the number of entries within a month on a stats sheet and when you add this to a graph, the dates/months will actually be in order

Paul Newcome

I'm just now seeing this. Haha.

But yes. Technically the only thing missing is that comma as well as the greater than/less than piece.

I personally would use a slightly different approach, but it is more personal preference and easier for me to manage. The first thing I would do is use an AND function anywhere I am repeating the same range. This just helps me keep all of my criteria for that range together, makes sure I don't type in the wrong range, etc..

=SUMIFS({Amount}, {Branch Number}, Label@row, {Request Type}, "Maintenance", {Date}, AND(@cell <= DATE(2024, 1, 31), @cell <= DATE(2024, 1, 1)))

The next thing I would do is specify the month and year when looking for a month instead of using a date range. It is easier for me to do it that way instead of trying to remember which months have 30 days, which have 31 days, and whether February is going to have 28 or 29 that particular year. Also makes for building out tables so I can use cell references and whatnot a little easier.

=SUMIFS({Amount}, {Branch Number}, Label@row, {Request Type}, "Maintenance", {Date}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024))

@MarceHolzhauzen Here's a formula I like to use to output a "text" for a month if that text is going to be the three characters.

=MID("12JanFebMarAprMayJunJulAugSepOctNovDec", MONTH(Date@row) * 3, 3)

MarceHolzhauzen

@Paul Newcome
I love how there are options here for every level and purpose. Probably my favourate part of Smartsheet is the fact that each user can have a way that works for them.

Thanks for this cool formula, I never thought of doing it that way! Much smaller formula than the one i had!

Paul Newcome

@MarceHolzhauzen I agree. I enjoy finding different ways to approach the same problem. Another thing I do for month text is (if I am pulling in the full month text) create a table that just has the month text listed down a column and then use an INDEX function (I hate nested IFs if they can be avoided).

=INDEX({Month Text Column}, MONTH(Date@row))

I have even gone so far as this below to avoid a nested IF when cross sheet references were limited:

=MID("1January2February3March4April5May6June7July8August9September10October11November12December13", FIND(MONTH(Date@row), "1January2February3March4April5May6June7July8August9September10October11November12December13") + 1, FIND(MONTH(Date@row) + 1, "1January2February3March4April5May6June7July8August9September10October11November12December13") - (FIND(MONTH(Date@row), "1January2February3March4April5May6June7July8August9September10October11November12December13")))

MarceHolzhauzen

@Paul Newcome
I often find it to be a balancing act between the user's knowledge of formulas and how they work and building something that is working.

Many users fall back on if and nested ifs because that is what they understand and can troubleshoot themselves.

I'm quite enjoying your approach here and if you don't mind, this is going to the go to formula bank 😉

Vanessa T.

This is amazing!! Thank you so much for all of your ideas!!! I am so thankful to be part of such a kind community!!!

Paul Newcome

@MarceHolzhauzen Very true. I do tend to pass along nested IFs, but I figured you'd be able to manage those other two. Feel free to steal away. Haha.

@Vanessa T. Happy to help. 👍️