Looking for help with a SUMIFS statement

Options
rtompkins
rtompkins
edited 12/09/19 in Smartsheet Basics

Good morning!

 

Looking for some help with the below SUMIFS statement.  I'm getting a couple different errors returned depending on my mood.  The challenge is the final criteron where I want to check a cell and return true if the cell date is within a certain range.

=SUMIFS([AC Size]:[AC Size], State:State, "MA", [PTO Forecast (UCC +30)]:[PTO Forecast (UCC +30)], true, [Utility Construction Completion +30]:[Utility Construction Completion +30], (AND(>=DATE(2019, 1, 1), <=DATE(2019, 1, 31))))

 

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You have a few extra parenthesis, and you will need to use the @cell reference. Try something like this...

     

    =SUMIFS(.............................ility Construction Completion +30], AND(@cell >=DATE(2019, 1, 1), @cell <=DATE(2019, 1, 31)))

     

    Another option if you are trying to look at a particular month and you don't want to have to worry about adjusting how many days are in that month, you could use

     

    AND(MONTH(@cell) = 1, YEAR(@cell) = 2019))

     

    You could also use different cells (dropdowns even) for the month and year and reference those. That way you don't have to edit the formula and worry about accidentally breaking something.

     

    AND(MONTH(@cell) = [Month Helper Column]@row, YEAR(@cell) = [Year Helper Column]@row))