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

Brendan Ginns

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})

Paul Newcome

Are you able to provide some screenshots?

Brendan Ginns

Brendan Ginns

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

Paul Newcome

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

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

Brendan Ginns

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?

Paul Newcome

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.

Brendan Ginns

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?

Paul Newcome

It would look somethign like this:

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

Brendan Ginns

=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.

Paul Newcome

What exactly is in Range 2?