Combining SUMIFS + Specific Date Range + Specific Criteria

Nancy Heater

Hello Smartsheet Community!

Hope you are all doing well!

I am stumped on how to achieve the results I'm looking for and am looking for some assistance. We are pulling data from one sheet to another, in this scenario.

Step 1, which I was able to figure out is to sum the number of "Potato Orders" in a certain date range: =SUMIFS({Potato Orders Range 1}, {Orders Shipped Range 2}, >=DATE(2022, 9, 18), {Orders Shipped Range 2}, <=DATE(2022, 9, 24))

Step 2, which I am struggling with ... I need to add in another criteria to the above formula, which would be that we only sum the Potato Orders in the specified date range that are for "Mike", in our Customer column. I am guessing this would require adding in COUNTIFS, but I'm not really sure and everything I have tried hasn't worked.

Any help would be greatly appreciated! Thank you as always!

Genevieve P.

Hi @Nancy Heater

This is actually quite simple! With the SUMIFS function you can simply add in the new range to look at and criteria into your current formula:

=SUMIFS({Potato Orders Range 1}, {Orders Shipped Range 2}, >=DATE(2022, 9, 18), {Orders Shipped Range 2}, <=DATE(2022, 9, 24), {Customer Column}, "Mike")

See: SUMIFS Function

That should then filter down your results! Let me know if that makes sense and works for you.

Cheers,

Genevieve

Nancy Heater

Hi @Genevieve P. - that did the trick! As usual, I was trying to make it much harder than it needed to be. Thank you so very much!

Have a great week!

Genevieve P.

Hi @Nancy Heater

This is actually quite simple! With the SUMIFS function you can simply add in the new range to look at and criteria into your current formula:

=SUMIFS({Potato Orders Range 1}, {Orders Shipped Range 2}, >=DATE(2022, 9, 18), {Orders Shipped Range 2}, <=DATE(2022, 9, 24), {Customer Column}, "Mike")

See: SUMIFS Function

That should then filter down your results! Let me know if that makes sense and works for you.

Cheers,

Genevieve

Nancy Heater

Hi @Genevieve P. - that did the trick! As usual, I was trying to make it much harder than it needed to be. Thank you so very much!

Have a great week!

Genevieve P.

No problem at all, I'm glad it was an easy update. 🙂

I hope you have a great week as well!

Jacob A

Hi @Genevieve P.

A follow up to this question, for some reason I created a report to show the break down of the days but the numbers are off. I used the "in between" filter on the report but the numbers are off and I am wondering why.

I have not had this issue in the past so I am wondering what is off. I had to come check to see if I was doing something wrong.

=SUMIFS({Proposed - LZ Waiting_Totals}, {Proposed - LZ Waiting_Create Date}, >=DATE(2024, 8, 1), {Proposed - LZ Waiting_Create Date}, <=DATE(2024, 8, 31))

Genevieve P.

Hi @jacob.alabi

Can you clarify what is "off"? It would be helpful to see the result of the formula, a screen capture of the source sheet, and a screen capture of your Report settings and Report result (but please block out sensitive data).

Thanks,

Genevieve

Jacob A

Hi @Genevieve P.

Thanks for the reply.

I am attached 3 screen capture. the source sheet, the report, and calculation sheet.

The results are same for all the months.

Screenshot 2024-10-01 075442.jpg

Screenshot 2024-10-01 074952.jpg

Screenshot 2024-10-01 074844.jpg

Genevieve P.

Hi @jacob.alabi

Thank you for this! It definitely helps. I can see that you're working with a Created Date system type of column.

This may be what is impacting your data, depending on where you're based.

See: https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones

System columns always use UTC in the back-end, while the display value matches the time zone of the last person to access the sheet. This means that, although the display value is in the sheet-viewers time zone, formulas and API calls using system column data may return based on the UTC time zone.

Jacob A

Hi @Genevieve P.

I think I know what the issue is. It's UTC conversion. What I don't understand is why the formula taking that into consideration now? Is this new?

I can't remember facing this before. I tried using the LZ Date for my calculation and it's matching ith the numbers from the created Report.

How do I fix this issue? Because it means a couple of my other sheets would be having the same issue too. Which by the way, we notice that when we write formulae to pull out the time from the Create column, the new Time column changes, between PST and CST, depending on who is using the sheet. We had to instruct everyone to set their time to CST.

Genevieve P.

Hi @jacob.alabi

Yes, I agree that since you're using a System Column the formula is recognizing the dates as UTC. Here's an article that has more information for you: https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones

I would suggest potentially using a different column for your calculations, such as a column with a date recorded when the row is created (using a Record a Date workflow).

Cheers,
Genevieve

Jacob A

Good morning @Genevieve P.

Thanks for the suggestion. I actually did that, the LZ Date column is a record date field, but we also need to capture time too. Unfortunately, we don't have that yet, either automation or column type. Though I submitted a product/new idea about it but not sure how long that would take to be developed.

For now, we would keep pulling out the time from Create and follow up with users that are throwing off the time with their time zone settings.

Thanks again for your kind assistance.
Jacob.