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
-
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.
Answers
-
What is the rest of your formula?
-
sorry
=SUMIFS({Quality Assurance Resource % Committed}, {Quality Assurance Range 1}, Names@row, {Quality Assurance Actual Finish}, >Mar19)
-
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.
-
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!!!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!