Counting Multiple Columns with different criteria
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!
Answers
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!