SUMIFS not working to sum after a given date

I am trying to sum a person's allocation if it is after a certain date. They might have 1-8 projects at one time with differing finish dates.

I can get it to work to sum a person's allocation without the date, =SUMIF({Quality Assurance Resource Assigned To}, [Project Status]@row, {Quality Assurance Resource % Committed}) returning 95%.

and I can get it to work if I only want to sum projects they are working on if it isn't a maintenance project =SUMIFS({Quality Assurance Resource % Committed}, {Copy of Copy of Copy of Quality Assurance Range 1}, [Project Status]@row, {Quality Assurance Maint Projects}, =0) returning 2%

but when I try =SUMIFS({Quality Assurance Resource % Committed}, {Quality Assurance Resource Assigned To}, Names@row, {Quality Assurance Actual Finish}, to get projects only finishing after Feb 1 I get 0% when it should be 95%

@Paul Newcome thanks in advance

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. Is "Mar19" a specific cell containing a date, are you wanting to count everything past March 19th, or are you trying to count anything greater than March 20219?

    There are a few options depending on your specifics.

    Greater than March 19th (assuming 2021):

    @cell > DATE(2021, 03, 19)

    Greater than March of 2019:

    @cell>= DATE(2019, 04, 01)

    If "Mar19" is a cell housing a specific date, then make sure that column is set as a date type column.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!