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

. 50

Find more posts tagged with

Sales

Finance

Formulas

Comments

GDMilk

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

GDMilk

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