Checked and unchecked box

I'm usually the first to assist in these situations, but I'm going to teach you to fish instead:

https://community.smartsheet.com/discussion/countifs-date 

This thread will give you the answer you desire.

All the best,

Chris McKay

Fishing Lesson (including the solution):

What you posted was simply an example someone else wrote out related to another sheet owned by another user, so don't panic. Although it looks like algebra or coding, it's a very simple formula and not that difficult to understand at all. You'll thank me later when you understand how it works.

Applications such as Smartsheet and Excel are built on the concept of using formulas to derive and manipulate data. You cannot escape it. Working in Smartsheet without formulas is like driving a car everywhere in first gear, with three wheels and the parking brake on.

Formulas always start out with an = sign. This tells Smartsheet (and Excel) that you are entering a formula, not a value (such as a date or dollar amount).

Formulas are also made up of functions. COUNTIFS is just one of them, as is TODAY. There are loads of them to choose from. If you type = into a cell in Smartsheet and then the first letter of a function (e.g. C), you will see a list of available functions pop up (see the image below).

These functions can be used on their own, or within other functions. You can also use arguments (i.e. criteria) and operators (+, -, >, < etc.) to create more specific and powerful results (but don't worry about that too much).

As an example, the following formula inserted in a cell within a date column will always display today's date (even when the date changes). I'm sure you can see a use for this, as it saves you having to manually change it every day:

=TODAY()

You don't really have to know how a formula is structured, to begin with. You will pick it up pretty quickly after you write a few and the application provides contextual help (visible if you attempt the above example).

Functions are always immediately followed by an open bracket "(". If you are not specifying anything else (as per the above formula), you will follow it with a closing bracket ")".

If you wanted to specify additional requirements, you can. This is usually contained within the two brackets referred to above. The following formula will display a 1 in the cell containing the formula if another cell contains a date that is less than today, otherwise leave the cell blank:

=IF([Your Date]1 < TODAY(), 1, "")

The IF function is one of the most common (and powerful) available and evaluates "if something is true, do this, otherwise, do this". Starting from left to right:

1. = tells Smartsheet that this is a formula
2. IF( opens the IF function allowing you specify a question and true or false options.
3. Referencing other cells in the current sheet within your formula is as simple as clicking on the other cell while you are editing the formula and the cursor is at the right spot (e.g. immediately after =IF( in the above example).
4. The question we are asking is "IF the date in the cell [Your Date]1 is less than today's date then do "something" but if it is not less than today's date then do "something else"
5. [Your Date]1 refers to the value that is in the first row of the Your Date column.
6. The "something" will be to populate the cell containing the formula with a number 1
7. The something else is to leave the cell containing the formula blank (defined by the "").
8. Parameters and criteria within the formula are separated by commas (,) as the contextual help will demonstrate.
9. For our purposes here, IF(this condition is true, do "something", otherwise do "something else")

There are loads and loads of other tricks, tips, conditions etc. that can be used and this is not the right place to post them. 

Wrapping all this up, give it a go. Create a blank sheet and play around. You really can't break anything and might actually learn something!

Now, your formula. To do what you wanted, try pasting this into the Amount 2 cell you highlighted in the image:

=IF(Cleared4, [Amount 1]4, "")

You should at least have a slightly clearer picture of what this does if you've read the above.

To apply the formula to the whole column, click and hold the tiny little "handle" visible at the bottom right of the cell and drag it all the way down the column as far as your data extends. From there on, Smartsheet will inherit the formula in new rows when they are added.

One final piece. I'd suggest right-clicking on the Amount 2 column header (at the top of your image) and selecting Lock Column. This prevents those that do not have Owner or Admin privileges in the sheet from deleting your formulas, as you want consistency.

Hope this helps.

Kind regards,

Chris McKay

You're welcome. I'm sure you learned something though