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.
- 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"
- 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"
- 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
-
@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?
Answers
-
@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?
-
Sorry Eric, it didn't upload. Thanks for replying.
-
@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?
- Pending Sales: =COUNTIFS([Date Contracted]:[Date Contracted], NOT(ISBLANK(@cell)), [Date Closed]:[Date Closed], ISBLANK(@cell))
- Open Spec: =COUNTIFS(Spec:Spec, 1, [Closed to Builder]:[Closed to Builder], 0)
- Closed Spec: =COUNTIFS(Spec:Spec, 1, [Closed to Builder]:[Closed to Builder], 1, [Date Closed]:[Date Closed], ISBLANK(@cell))
-
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 #
-
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"
-
@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.
-
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
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
Check out the Formula Handbook template!