COUNTIF to count cells with a date less than, and ignore blank cells

Options

Good afternoon.

I am trying to count some data in a Date Closed column.

  1. I want to count the number of cells showing a date less than 4/30/21. There are some blank cells that I am not sure how to account for.

=COUNTIF({Circle K Summary Date Closed}, @cell <= DATE(2021, 4, 30))

  1. I am also going to want to sum the amounts in a Final Sale Price column as related to these dates. For instance, sum all sales prices in the smartsheet column for closing less than 4/30/21.

Appreciate the help. I keep playing with it. Might come to me in the middle of the night and I am reading a ton of posts, but just can't get it right.


Thanks.

Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Options

    COUNTIF will not include blank cells so you should be good with the blank cells.

    For the sum use the same expression with SUMIF function like,

    =SUMIF({Circle K Summary Date Closed}, <= DATE(2021, 4, 30), [Final Sale Price]:[Final Sale Price])

  • french2s
    Options

    The Countif is working. I had a mistake in my summary sheet.

    If I want it between dates - such as between 5/1/22 and 4/30/23, what does that formula look like?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!