SUMIFS not working to sum after a given date

Bill Schroeder

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

Paul Newcome

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.

Paul Newcome

What is the rest of your formula?

Bill Schroeder

sorry

=SUMIFS({Quality Assurance Resource % Committed}, {Quality Assurance Range 1}, Names@row, {Quality Assurance Actual Finish}, >Mar19)

Paul Newcome

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.

Bill Schroeder

The Mar19 was a column and row (so cell). What fixed it was making the column "date" vs "text/number". As soon as I did that it went from 0% to 95%, which is what I would expect to see!!

Thank you so much!!!!

Paul Newcome

Happy to help. 👍️