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

Mark Schlaudraff

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!

Mark Cronk

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

Paul Newcome

@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

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

Mark Schlaudraff

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

Makes perfect sense now. Thank you.

Mark

Mark Schlaudraff

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

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

Paul Newcome

@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

Thanks @Paul Newcome ! Nice catch.

Mark

Mark Schlaudraff

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

Paul Newcome

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