Formula error

tmartin

I want to create a sheet summary of some data.

One formula that will count the number of cells in the Status column that contain "SPEC" if the Closed to Builder Box NOT checked.

One formula that will count the number of cells in the Status column that contain "SPEC" if the Closed to Builder Box NOT checked.

Danielle Arteaga

To count the number of rows with Status of "SPEC" and Closed to Builder box is NOT checked:

=COUNTIFS(Status:Status, "SPEC", [Closed to Builder]:[Closed to Builder], 0)

To count the number of rows with Status of "SPEC" and Closed to Builder box IS checked:

=COUNTIFS(Status:Status, "SPEC", [Closed to Builder]:[Closed to Builder], 1)

(You can place these formulas in the fields on your Sheet Summary.)

Danielle Arteaga

To count the number of rows with Status of "SPEC" and Closed to Builder box is NOT checked:

=COUNTIFS(Status:Status, "SPEC", [Closed to Builder]:[Closed to Builder], 0)

To count the number of rows with Status of "SPEC" and Closed to Builder box IS checked:

=COUNTIFS(Status:Status, "SPEC", [Closed to Builder]:[Closed to Builder], 1)

(You can place these formulas in the fields on your Sheet Summary.)

tmartin

THANK YOU!!! You're awesome!

Can we also get a formula if the status is sold but the closed to builder is not checked??

And

And another formula with lots with nothing the status column?

Danielle Arteaga

=COUNTIFS(Status:Status, "Sold", [Closed to Builder]:[Closed to Builder], 0)

Counting "blank" cells in Smartsheet is a bit of a challenge, because all Smartsheet grids have "phantom" rows (5-10 blank rows that are automatically inserted at the end of every worksheet), and formula-based cells are not always blank.

You can try these:

=COUNTIF(Status:Status, "")

or

=COUNTIF(Status:Status, ISBLANK(@cell))

One final way to do this is to count the number of non-blank cells in a column that is always populated. For example, assume your Lot column is never blank, you could do this:

=COUNT(Lot:Lot)-COUNTM(Status:Status)

The COUNT of Lot will give you the total number of rows that have data. The COUNTM of Status gives you the total number of rows in the status column that are NOT blank. The difference between the two should yield the number of blank Status cells.

You'll have to play around with these to see what is most reliable for your sheet. Good luck!