# How To Set Up A SUMIFS Cross Sheet Formula that Considers a Date Range.

Options
✭✭✭✭
edited 06/15/22

Sheet 1: Entry Log

Sheet 2: Allocated Hours

Hi, I have built a formula that sums the hours from the entry log and pulls them into allocated hours. however, the cell that I am pulling the sum into is date sensitive. I am trying to build a range around the formula that takes into account the (LOG Date) and the Cell Date i.e (Week 1) (Every 7 Days ) Please let me know if someone knows how to add to this and make it a SUMIFS

=SUMIF({Entry Log Range 1}, Description@row, {Entry Log Range 2})

• ✭✭✭✭✭✭
Options

Are you able to provide some screenshots?

• ✭✭✭✭
Options
• ✭✭✭✭
Options

so the coloum that says week 1, I'm trying to make the SUM time-sensitive, yet leave the criterion range the same.

• ✭✭✭✭✭✭
Options

You are going to have to change over to a SUMIFS.

=SUMIFS({Numbers}, {Description}, @cell = Description@row, {Dates}, date_criteria)

• ✭✭✭✭
Options

Ok great,from the {log date}, Date_Criteria} would you recommend a range ? or would a greater than equal to be sufficient? say if the last day of the week is 03/15/22 and the Log Date is 03/16/22 it would fall into Week 2, but if it was 03/15/22 it would still be week 1?

• ✭✭✭✭✭✭
Options

That is going to depend on what you need. If you need it to be between two dates then you would need to use a date range, but if it just needed to be greater than a certain date then you would just use that.

• ✭✭✭✭
Options

I feel like the range is a safer option seeing that the weeks are pre determined. Do you have and example of the syntax for the date range added to the Sumifs?

• ✭✭✭✭✭✭
Options

It would look somethign like this:

=SUMIFS({Numbers}, {Description}, @cell = Description@row, {Dates}, AND(@cell >= DATE(2022, 06, 12), @cell <= DATE(2022, 06, 18)))

• ✭✭✭✭
Options

=SUMIFS({Entry Log Range 2}, {Entry Log Range 1}, @cell = Description@row, {Entry Log Range 3}, AND(@cell <= DATE(2022, 3, 12), @cell >= DATE(2022, 3, 15)))

This is what the formula ended up as, it worked. However, the sum is 0 and I just put an entry in for a description of the 14th of march for 2 hours and still no changes.

• ✭✭✭✭✭✭
Options

What exactly is in Range 2?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!