Supply a count of a select status based on a date range, then supply a sum...

Options

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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

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

    @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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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 Schlaudraff
    Options

    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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Makes perfect sense now. Thank you.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark Schlaudraff
    Options

    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)

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 12/22/20
    Options

    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.

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

    @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))

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Thanks @Paul Newcome ! Nice catch.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark Schlaudraff
    Options

    @Paul Newcome & @Mark Cronk : You all are the BEST!!! THank you so much! That fixed it Paul and Mark.

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

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!