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

Here you go! Hope this helps. =COUNTIFS([Created Date]:[Created Date], >=DATE(2022, 5, 1), [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")

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

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

Answers

Paul Newcome ✭✭✭✭✭✭

06/14/22

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

CERTIFIED SMARTSHEET PLATINUM PARTNER
10xViz.com
0 · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Get a Countif on values in a multi-select column of cells
I am trying to write summary formulas to determine how many values appear of each in the drop-down of a column. I tried adding a wildcard, I have tried HAS, and tried CONTAIN, but I'm still not getting the right values. Currently I am getting 0 as a return value on this formula: =COUNTIF([ROI Type]:[ROI Type], HAS([ROI…
summaries in report
Hi i'm trying to build a report with summaries options. i have two column that i want a Min and Max summary. the column for the Min i a texte column and works well. but the other column has formulas and the Max doesn't works. there is a way to fix that? (instead of creating another text column in the source sheets?) here…
Converting email addresses with hyphenated First and/or Last Names
I am trying to convert email addresses into a Name field with the employee's First and Last Name, capitalizing the first character of each part of the name and keeping the hyphen in any part of the name that has one. I started with hyphenated first names so taking shmoe_joe-bob@company.com and converting it into Joe-Bob…

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

Answers

Help Article Resources

Categories