Value for Criteria met

tmartin

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

Eric Law

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

Eric Law

@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

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

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

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

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

@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

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