COUNTIF(S) to sum number of issues completed in a given range

AnonUser1

Looking to create a sheet summary and need a formula that will return the number of items with a creation date between 05/01/2022 - 05/31/2022 that also have the build status marked as "Complete" or "Delivered".

Tried these two formulas, both return as "unparseable"

=COUNTIFS(MONTH([Complete Date]5), [Build Status]:[Build Status], "Complete"])

=COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), Build Status:Build Status, @cell = "Complete")

Dan W

Here you go! Hope this helps.

=COUNTIFS([Created Date]:[Created Date], >=DATE(2022, 5, 1), [Created Date]:[Created Date], <=DATE(2022, 5, 31), [Build Status]:[Build Status], [Build Status]:[Build Status] = "Complete") + COUNTIFS([Created Date]:[Created Date], >=DATE(2022, 5, 1), [Created Date]:[Created Date], <=DATE(2022, 5, 31), [Build Status]:[Build Status], [Build Status]:[Build Status] = "Delivered")

Paul Newcome

@AnonUser1 The unparseable error is coming from some of your quotes. So how "these quotes" are straight up and down and some of your quotes are slanted and show open vs closed? Those slanted ones are not recognized as valid characters in Smartsheet. Try retyping it here in the community, directly in the sheet, or in a text editor such as Notepad. Programs such as Word will always give you those quotes that are not recognizable.

As for the additional criteria, splitting it out into multiple COUNTIFS and adding them together is going to become rather unwieldy rather quickly. With having three different ranges with two different criteria, you would now need to write out a lot more of them and add them together (one for each possible variation would be 8 of them). Using the OR function is going to give you a much cleaner and more efficient formula.

=COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], OR(@cell = "Complete", @cell = "Delivered"), Env:Env, OR(@cell = "QA", @cell = "QA 2"), Priority:Priority, OR(@cell = "High", @cell = "Highest - showstoppers"))

vs adding multiple COUNTIFS together (not recommended especially if there is the potential to need to expand on the ranges and/or criteria):

=COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Complete", Env:Env, @cell = "QA", Priority:Priority, @cell = "High") + COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Complete", Env:Env, @cell = "QA", Priority:Priority, @cell = "Highest - showstoppers") + COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Complete", Env:Env, @cell = "QA 2", Priority:Priority, @cell = "High") + COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Complete", Env:Env, @cell = "QA 2", Priority:Priority, @cell = "Highest - showstoppers") + COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Delivered", Env:Env, @cell = "QA", Priority:Priority, @cell = "High") + COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Delivered", Env:Env, @cell = "QA", Priority:Priority, @cell = "Highest - showstoppers") + COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Delivered", Env:Env, @cell = "QA 2", Priority:Priority, @cell = "High") + COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Delivered", Env:Env, @cell = "QA 2", Priority:Priority, @cell = "Highest - showstoppers")

.

To write it in a separate sheet, you would copy/paste the same exact formula into the other sheet. It will probably throw an error, but once you finish correcting the ranges it should be good to go.

Highlight each range:

[Column Name]:[Column Name]

In the little formula helper box that pops up, there should be a link to "Reference another sheet". Click on that, select the source data sheet, click on the appropriate column header (optionally and recommended change the reference name so that it reflects sheet and column for easier troubleshooting just in case), then click on the blue box in the bottom right corner to "Insert Reference".

Now [Column Name]:[Column Name] should be replaced with {Range Name}. Do that for each of the remaining ranges, and you should be good to go.

AnonUser1

@Paul Newcome I've seen you answer similar related questions, any ideas? Thank you in advance!

Dan W

Here you go! Hope this helps.

=COUNTIFS([Created Date]:[Created Date], >=DATE(2022, 5, 1), [Created Date]:[Created Date], <=DATE(2022, 5, 31), [Build Status]:[Build Status], [Build Status]:[Build Status] = "Complete") + COUNTIFS([Created Date]:[Created Date], >=DATE(2022, 5, 1), [Created Date]:[Created Date], <=DATE(2022, 5, 31), [Build Status]:[Build Status], [Build Status]:[Build Status] = "Delivered")

Paul Newcome

@AnonUser1 The reason for the error in the second formula of your original post is that column names containing spaces, numbers, and/or special characters should be wrapped in [Square Brackets]. So the "Build Status" should be wrapped the same way you have "[Created Date]" wrapped.

To count for two different statuses, you can use an OR function like so (square brackets also corrected):

=COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], OR(@cell = "Complete", @cell = "Delivered"))

Paul Newcome

@Dan W I haven't tested, but I am not sure your formula will work correctly because of the bold portion.

=COUNTIFS([Created Date]:[Created Date], >=DATE(2022, 5, 1), [Created Date]:[Created Date], <=DATE(2022, 5, 31), [Build Status]:[Build Status], [Build Status]:[Build Status] = "Complete") + COUNTIFS([Created Date]:[Created Date], >=DATE(2022, 5, 1), [Created Date]:[Created Date], <=DATE(2022, 5, 31), [Build Status]:[Build Status], [Build Status]:[Build Status] = "Delivered")

You are going to want to replace the full column reference with an "@cell" reference.

Dan W

@Paul Newcome I tested it in a Sheet summary field prior to posting.👍️

AnonUser1

It worked for me in the summary field @Dan W , thank you so much.

One additional question -- If I want to use this formula in a separate sheet in a cell (rather than a summary sheet w/n the original sheet)

1. Env (QA and QA2)
2. Priority (Highest and High)
3. Build Status (Complete or Delivered)
4. Created Date (May 2022)

How would I adjust this formula now that I will 1) be referencing the original sheet and 2) will be using a total of four criteria. ? Still confused...thanks again!

I tried this (within the summary field)

=COUNTIFS([Created Date]:[Created Date], >=DATE(2022, 5, 1), [Created Date]:[Created Date], <=DATE(2022, 5, 31), [Build Status]:[Build Status], [Build Status]:[Build Status] = "Complete”, [Env]:[Env], [Env]:[Env] = “QA”, [Priority]:[Priority], [Priority]:[Priority] = “High”) + COUNTIFS([Created Date]:[Created Date], >=DATE(2022, 5, 1), [Created Date]:[Created Date], <=DATE(2022, 5, 31), [Build Status]:[Build Status], [Build Status]:[Build Status] = "Delivered", [Env]:[Env], [Env]:[Env] = “QA2”, [Priority]:[Priority], [Priority]:[Priority] = “Highest”)

Which returns unparseable

Also, is there a way to simply this formula?

AnonUser1

@Dan W thank you, your original solution worked when selecting two criteria and adding it within a sheet summary.

Now, I am trying to add four criteria, and enter the formula in a separate sheet where I reference the original sheet. The four criteria are:

1. Env (QA and QA 2)
2. Priority (Highest and High)
3. Created Date (May 2022)
4. Build Status (Complete or Delivered)

I tried using the formula below which returns unparseable (within a sheet summary), still don't know how to add this to a separate sheet and add the formula to a cell.

=COUNTIFS([Created Date]:[Created Date], >=DATE(2022, 5, 1), [Created Date]:[Created Date], <=DATE(2022, 5, 31), [Build Status]:[Build Status], [Build Status]:[Build Status] = "Complete”, [Env]:[Env], [Env]:[Env] = “QA”, [Priority]:[Priority], [Priority]:[Priority] = “High”) + COUNTIFS([Created Date]:[Created Date], >=DATE(2022, 5, 1), [Created Date]:[Created Date], <=DATE(2022, 5, 31), [Build Status]:[Build Status], [Build Status]:[Build Status] = "Delivered", [Env]:[Env], [Env]:[Env] = “QA”, [Priority]:[Priority], [Priority]:[Priority] = “Highest - showstoppers”)

Thanks again!

Paul Newcome

@AnonUser1 The unparseable error is coming from some of your quotes. So how "these quotes" are straight up and down and some of your quotes are slanted and show open vs closed? Those slanted ones are not recognized as valid characters in Smartsheet. Try retyping it here in the community, directly in the sheet, or in a text editor such as Notepad. Programs such as Word will always give you those quotes that are not recognizable.

As for the additional criteria, splitting it out into multiple COUNTIFS and adding them together is going to become rather unwieldy rather quickly. With having three different ranges with two different criteria, you would now need to write out a lot more of them and add them together (one for each possible variation would be 8 of them). Using the OR function is going to give you a much cleaner and more efficient formula.

=COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], OR(@cell = "Complete", @cell = "Delivered"), Env:Env, OR(@cell = "QA", @cell = "QA 2"), Priority:Priority, OR(@cell = "High", @cell = "Highest - showstoppers"))

vs adding multiple COUNTIFS together (not recommended especially if there is the potential to need to expand on the ranges and/or criteria):

=COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Complete", Env:Env, @cell = "QA", Priority:Priority, @cell = "High") + COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Complete", Env:Env, @cell = "QA", Priority:Priority, @cell = "Highest - showstoppers") + COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Complete", Env:Env, @cell = "QA 2", Priority:Priority, @cell = "High") + COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Complete", Env:Env, @cell = "QA 2", Priority:Priority, @cell = "Highest - showstoppers") + COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Delivered", Env:Env, @cell = "QA", Priority:Priority, @cell = "High") + COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Delivered", Env:Env, @cell = "QA", Priority:Priority, @cell = "Highest - showstoppers") + COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Delivered", Env:Env, @cell = "QA 2", Priority:Priority, @cell = "High") + COUNTIFS([Created Date]:[Created Date], AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 5), [Build Status]:[Build Status], @cell = "Delivered", Env:Env, @cell = "QA 2", Priority:Priority, @cell = "Highest - showstoppers")

.

To write it in a separate sheet, you would copy/paste the same exact formula into the other sheet. It will probably throw an error, but once you finish correcting the ranges it should be good to go.

Highlight each range:

[Column Name]:[Column Name]

In the little formula helper box that pops up, there should be a link to "Reference another sheet". Click on that, select the source data sheet, click on the appropriate column header (optionally and recommended change the reference name so that it reflects sheet and column for easier troubleshooting just in case), then click on the blue box in the bottom right corner to "Insert Reference".

Now [Column Name]:[Column Name] should be replaced with {Range Name}. Do that for each of the remaining ranges, and you should be good to go.

AnonUser1

Thank you @Paul Newcome, the OR function is much cleaner, and I was able to successfully populate the information I needed using sheet summary, although some of the numbers are off, but I will need to check the manual calculations.

Now, when trying to use the "reference another sheet" function in the other sheet, I am unable to select the columns because they are not adjacent to each other? Any workaround for this?

AnonUser1

I figured it out -- I just had to select each column one at a time, but how do I replace the "Sheet Name - Range 1" with the proper column name as written on the reference sheet?

Paul Newcome

You would click inside of the reference {anywhere in here} and you should see the link in the helper box now says "Edit Reference". Click on that to edit the cross sheet reference. Then near the top right corner of the new window you should see the Range Name. You can edit it there then click on the blue box in the bottom right corner to update the reference.

AnonUser1

Thank you for all your help, I figured it out shortly after I posted!

AnonUser1

@Paul Newcome I'm trying to find the average age of open defects within a certain environment with a certain priority and came up with this:

=AVG(COLLECT([Age from Created Date]:[Age from Created Date], Env:Env, OR(@cell = "Prod", @cell = "Prod 2", Priority:Priority, (@cell = "Highest - showstoppers"))))

This returns an invalid data type error...any thoughts?

Paul Newcome

You have a few misplaced parenthesis. Try this:

=AVG(COLLECT([Age from Created Date]:[Age from Created Date], Env:Env, OR(@cell = "Prod", @cell = "Prod 2"), Priority:Priority, @cell = "Highest - showstoppers"))

AnonUser1

@Paul Newcome That worked, it was just missing a parentheses, which I added.

I'm running into an "invalid argument set" error when trying to use NOT logiC

=COUNTIFS({Status}, NOT(@cell = "Done: Defect Complete", @cell = "Done: Canceled/Duplicate"), {Env}, (@cell = "QA"), {Priority}, (@cell = "Highest - showstoppers"))

There are 10 available statuses, I would rather list the two statuses that should not be checked, for the issue to be marked as open, than to list the 8 statuses, it could be checked.

Do you see any error in my formula? Was working fine, until I added the NOT