Value for Criteria met

I am VERY unfamiliar with Smartsheet and trying to get acquainted.... I keep getting stuck. I need help gathering values for the sheet summary. I hope to use this platform in our office, however I need assistance in this before I can sell it.

  1. If there is a checkmark in "Date Contracted" but NO DATE in "Date Closed" I need that value to populate as "1". If Both Criteria is reached then I would need the value to go to "0" for my "Pending Sale"
  2. If there is a checkmark in "Spec" and there is NOT one "Closed to Builder" = I would need that to populate a value of "1" but if there is a date in "Closed to Builder" then I would need that to remove the value and omit it this is for my "Open Spec"
  3. If there is a check mark in "Spec" and there IS a one "Closed to Builder" = I would need that to populate a value of "1" but if there is a date in "Date Closed" then I would need that to remove the value and omit it. This is for my "Closed Spec

Best Answer

Answers

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓

    @tmartin Can you provide a screenshot? From what you have outlined, I don't understand what sheet summary you need. It seems like you need a formula to help populate a cell?

  • tmartin
    tmartin ✭✭

    Sorry Eric, it didn't upload. Thanks for replying.

  • Eric Law
    Eric Law ✭✭✭✭✭✭

    @tmartin K, it would be good with some examples of what you want but I am guessing you want the following

    Some notes, you mentioned Date Contracted was a checkbox but it doesn't look like one. I will assume you will keep it as a date as it makes more sense. Try these?

    1. Pending Sales: =COUNTIFS([Date Contracted]:[Date Contracted], NOT(ISBLANK(@cell)), [Date Closed]:[Date Closed], ISBLANK(@cell))
    2. Open Spec: =COUNTIFS(Spec:Spec, 1, [Closed to Builder]:[Closed to Builder], 0)
    3. Closed Spec: =COUNTIFS(Spec:Spec, 1, [Closed to Builder]:[Closed to Builder], 1, [Date Closed]:[Date Closed], ISBLANK(@cell))
  • tmartin
    tmartin ✭✭

    ERIC, You are awesome dude! Thank you! It worked perfect.


    Last question.... Is there anyway to make the closed to builder not a decimal? When I add decimal or subtract it wont give a whole #

  • tmartin
    tmartin ✭✭

    One more question.

    Can I create a calculation for a sheet summary if I want to know the difference of the " closed to builder"?

    There are 118 lots so each time one closes, or a mark is checked I would like it to subtract that number. It would be a separate summary that says "Not Closed"

  • Eric Law
    Eric Law ✭✭✭✭✭✭

    @tmartin You should check your formula about the decimal. Also, you can click into that and click on the numbers icon to change the decimal value.

    I don't know what your other questions means as I don't know what qualifies for that.

  • tmartin
    tmartin ✭✭

    When I change the value, it won't go to a whole number. The formula I have it

    =COUNTIF([Closed to Builder]1:[Closed to Builder]118, =1) / 118

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!