Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Countif a value AND Completed Date not Blank
Struggling with the right equation. Completed Date Column has no end range (rows can continue to be added at anytime via a form). Size Column has many different sizes 32, 40, 50, 60, 72, 80, 100, 106 that can be in the column. In additional columns I have a column for each size. I want to count how many size 32 I have IF completed date not blank. Same with each size.
Completed Date, Size 32 40 50 .,............
05/31/2017 32 (Ans would be 1) 0 0 32
32
40
. 50
Comments
-
For Clarity, In Column (32)1 I want the # of size 32 I have in the entire range is (Size:Size) only if there is a Completed Date (or not blank Completed Date)
Thanks
-
I can do it easily if I add another column say called Completed Size, do a ISDATE, only return the size value from the Size column into the new column and then do a COUNTIF off the new Completed Size Column. Just seems like should be able to do it in one equation without creating a dummy calculation column.
So in Completed Date Column =IF(ISDATE([Completed Date]1, Size1, ""))
Then in 32 Column =COUNTIF([Completed Size]:[Completed Size], 32) and so on for each size count results column
Thing is I also have four different columns of hours as well. They all need the same thing, to only be summed if there is a completed date filled in. Because with the SmartSheet filling in of equations, the summary columns will fill in with zeros until the hours are filled in at the end of a project. Thus those zeros, show up in averages calcs and so on. I need to truncate those out or all equations until there is a completed date filled in. So if this size count can be done in one single equation wo making additional columns then that pattern of equation will also work for the other columns with hours in them. Same situation (format) as the size column, can be infinite rows being added all the time so I don't know the end row value needs to be a range.....
-
This any good?
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives