Calculating a column based on date

ginamt3

I am now running into the issue where my formula is counting weeknumbers over 2018 and 2019. How would I fix this in this formula to just pick up the weeknumbers in 2019?

=SUMIFS({MASTER SCHEDULE Range 4}, {MASTER SCHEDULE Range 5}, IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = 36)

Paul Newcome

We would simply use an AND statement to include a YEAR function in the criteria.

 

=SUMIFS({MASTER SCHEDULE Range 4}, {MASTER SCHEDULE Range 5}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = 36, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2019))

ginamt3

It's still totaling week numbers that are future.. see attached

Paul Newcome

I am not sure what you mean...

 

The formula above will sum all cells in range 4 who's date in range 5 falls within week number 25 of the year 2019.

 

What is the desired outcome?

ginamt3

week 38 is 9/15

week 39 is 9/22

Neither of those dates are in the range for 2019. 

We enter those dates as we get retainers. it should be zero until we receive a retainer and someone records it. 

Paul Newcome

You would need to include that logic in your SUMIFS formula.

ginamt3

week 38 is 9/15

week 39 is 9/22

Neither of those dates are in the range for 2019. 

We enter those dates as we get retainers. it should be zero until we receive a retainer and someone records it.