Counting Multiple Columns with different criteria

IamRachal

Hello!

We manage properties with under different statuses (Available, Expired, Under Contract, etc.) and use another Column called "Dead" to filter our reports. We created a checks and balance sheet to view the counts at a glance for high level overview, and we are unsure how to use the =COUNT and =COUNTIFs on two columns.

For clarity, our property statuses are: Available, Leased, Fully Leased, Under Contract, Withdrawn, Sold and would like for the Dead column (check box option) to update the count in the formula.

For example, if Expired and checked "Dead", do not count. Has anyone else tried complex formulas on two variable columns?

=COUNTIFS({Listings Sheet}, {Expired}) works to count the number of "Expired" we need a formula with the consideration of the "Dead" column checked to not count. Thank you!

Capture.PNG

Capture.PNG

David Joyeuse

Hi @IamRachal

Since you need to check for 2 things, you need to use a COUNTIFS function. And I would advise you to always prefer COUNTIFS/SUMIFS to COUNTIF/SUMIF. They work perfectly fine for 1 or more criteria.

According to your pics you could go on with this (in your [count] column of the second pic):

=COUNTIFS({Property Status}, [Listings (& their Status)]@row, {Dead}, 0)

{Property Status} being the link to the whole [Property Status] of your first pic, and {Dead} the range of the whole [Dead] column of the first pic too.

Checkboxes are counted like this in Smartsheet:

0 if the box is unchecked.

1 if it is checked.

Hope it helped!