Supply a count of a select status based on a date range, then supply a sum...
Good Morning SmartSheet Helper Gurus....
I have the need to get the number of Status = Closed for a select date range, then tell me the Sum of the Story Points column for those closed tickets.
I can get my number of closed ticket with :
=COUNTIFS({Story Point Reference}, FIND([Primary Column]2, @cell) > 0)
I can get my number of tickets within a date range with :
=COUNTIFS({Story Point Connector ( PID ) Creation Date}, <=DATE(2020, 10, 31), {Story Point Connector ( PID ) Creation Date}, >=DATE(2020, 10, 1), ({Story Point Reference}, FIND([Primary Column]2, @cell) > 0) )
However, that is where I get lost... I need to date one to only pull the closed tickets and also need to add in the sum total of the story point column for those said close tickets.
HELP!
Best Answers
-
Hi @Mark Schlaudraff ,
You number of tickets COUNTIFS should be what you're looking for. It counts the stories created between 10/1- 10/31/20 that are closed. You had an extra set of parens, which I removed. Are you getting an error or just the wrong result? :
=COUNTIFS({Story Point Connector ( PID ) Creation Date}, <=DATE(2020, 10, 31), {Story Point Connector ( PID ) Creation Date}, >=DATE(2020, 10, 1), {Story Point Reference}, FIND([Primary Column]2, @cell) > 0)
To get the sum you'll use SUMIFS. You want the same criteria at your COUNTIFS but want to sum the story points; placeholder in formula below. Your formula would be:
=SUMIFS({/insert story point range/}, {Story Point Connector ( PID ) Creation Date}, <=DATE(2020, 10, 31), {Story Point Connector ( PID ) Creation Date}, >=DATE(2020, 10, 1), {Story Point Reference}, FIND([Primary Column]2, @cell) > 0)
Out of curiosity, why are you using a FIND function to determine if a story is closed? Usually a closed tag is a date or a check box.
Hope this is of some help.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
@Mark Cronk I believe your OR function may be misplaced.
=SUMIFS({Story Point Connector ( PID ) Story Point Totals}, {Story Point Connector ( PID ) Creation Date}, <=DATE(2020, 10, 31), {Story Point Connector ( PID ) Creation Date}, >=DATE(2020, 10, 1), {Story Point Connector ( PID ) Work Types}, FIND([Primary Column]59, @cell) > 0, {Story Point Reference}, OR(FIND([Primary Column]20, @cell) > 0, FIND([Primary Column]21, @cell) > 0, FIND([Primary Column]22, @cell) > 0))
Answers
-
Hi @Mark Schlaudraff ,
You number of tickets COUNTIFS should be what you're looking for. It counts the stories created between 10/1- 10/31/20 that are closed. You had an extra set of parens, which I removed. Are you getting an error or just the wrong result? :
=COUNTIFS({Story Point Connector ( PID ) Creation Date}, <=DATE(2020, 10, 31), {Story Point Connector ( PID ) Creation Date}, >=DATE(2020, 10, 1), {Story Point Reference}, FIND([Primary Column]2, @cell) > 0)
To get the sum you'll use SUMIFS. You want the same criteria at your COUNTIFS but want to sum the story points; placeholder in formula below. Your formula would be:
=SUMIFS({/insert story point range/}, {Story Point Connector ( PID ) Creation Date}, <=DATE(2020, 10, 31), {Story Point Connector ( PID ) Creation Date}, >=DATE(2020, 10, 1), {Story Point Reference}, FIND([Primary Column]2, @cell) > 0)
Out of curiosity, why are you using a FIND function to determine if a story is closed? Usually a closed tag is a date or a check box.
Hope this is of some help.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hey @Mark Cronk, thanks for this information...
I'll give it a try... As to the question about FIND... All the data is coming in from JIRA so the status column can have more than one status, so I find all the CLOSED via FIND.
I'll let you know how it goes here in a little bit, a big Thank You for your reply.
Mark
-
Makes perfect sense now. Thank you.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hey @Mark Cronk , just wanted to reach back out and say THANK YOU, that fixed my issue. Now I do have one more question, below is my new entry I'm trying to do, I need the SUM of the Story Points for a select Work type if the status is one of the three, but I'm getting a UNPARSEABLE error, what am I missing:
=SUMIFS({Story Point Connector ( PID ) Story Point Totals}, {Story Point Connector ( PID ) Creation Date}, <=DATE(2020, 10, 31), {Story Point Connector ( PID ) Creation Date}, >=DATE(2020, 10, 1), {Story Point Connector ( PID ) Work Types}, FIND([Primary Column]59, @cell) > 0, {Story Point Reference}, FIND([Primary Column]20, @cell) > 0, {Story Point Reference} FIND([Primary Column]21, @cell) > 0, {Story Point Reference}, FIND([Primary Column]22, @cell) > 0)
-
Hi @Mark Schlaudraff ,
To match 1 of 3 you need to use an OR function and you were missing a comma:
=SUMIFS({Story Point Connector ( PID ) Story Point Totals}, {Story Point Connector ( PID ) Creation Date}, <=DATE(2020, 10, 31), {Story Point Connector ( PID ) Creation Date}, >=DATE(2020, 10, 1), {Story Point Connector ( PID ) Work Types}, FIND([Primary Column]59, @cell) > 0, {Story Point Reference}, OR(FIND([Primary Column]20, @cell) > 0, FIND([Primary Column]21, @cell) > 0, FIND([Primary Column]22, @cell) > 0)))
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
@Mark Cronk I believe your OR function may be misplaced.
=SUMIFS({Story Point Connector ( PID ) Story Point Totals}, {Story Point Connector ( PID ) Creation Date}, <=DATE(2020, 10, 31), {Story Point Connector ( PID ) Creation Date}, >=DATE(2020, 10, 1), {Story Point Connector ( PID ) Work Types}, FIND([Primary Column]59, @cell) > 0, {Story Point Reference}, OR(FIND([Primary Column]20, @cell) > 0, FIND([Primary Column]21, @cell) > 0, FIND([Primary Column]22, @cell) > 0))
-
Thanks @Paul Newcome ! Nice catch.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
@Paul Newcome & @Mark Cronk : You all are the BEST!!! THank you so much! That fixed it Paul and Mark.
-
Happy to help.
I personally would also use an AND function when looking at the dates. It allows you to only have to enter the range once, and it helps keep the data organized (in my opinion).
=SUMIFS({Story Point Connector ( PID ) Story Point Totals}, {Story Point Connector ( PID ) Creation Date}, AND(@cell<=DATE(2020, 10, 31), @cell>=DATE(2020, 10, 1)), {Story Point Connector ( PID ) Work Types}, FIND([Primary Column]59, @cell) > 0, {Story Point Reference}, OR(FIND([Primary Column]20, @cell) > 0, FIND([Primary Column]21, @cell) > 0, FIND([Primary Column]22, @cell) > 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!