Cross sheet formula  help
With this new feature I'm hoping I can eliminate abut 10 columns from one sheet and create a sheet just for totals.
Currently I have several columns just for counting the value of columns with checkboxes. Do I have to leave these in the same sheet? When I tried to do the values in a new sheet by referencing the main sheet it didn't work.
Also, I had ended up creating checkbox columns for several locations (checkin boxes) but since I have a dropdown column for the locations is there a way to have one check in column on the main sheet and use a formula to count the checkbox column using the specified criteria from the location column?
I've watched several videos over and over and while it seemed it should work it didn't.
Comments

Hello,
Thanks for the question. From the sounds of it, you're looking to count the number of checked boxes in a column where a dropdown column is a specified value. This can be done using the COUNTIFS function, and can be done on another sheet if you'd like. Check out this help center article on referencing data in another sheet within formulas for more information on this feature (https://help.smartsheet.com/articles/2476606).
Here's an example of how this formula could be written if it were on the same sheet as the data it's referencing:
=COUNTIFS([Checkbox Column]:[Checkbox Column], 1, [Dropdown Column]:[Dropdown Column], "Location 1")
This will count rows where the column named "Checkbox Column" is checked (1 means checked and 0 means unchecked) and the column named "Dropdown Column" is set to "Location 1". If you're looking to have these formulas on a different sheet than where the data is, the formula would be the same however instead of referencing the column names you reference another sheet. Here is a basic overview of the steps for how to create this formula:
 On the sheet you'd like to put the formula, start by typing this
 =COUNTIFS(
 Click the "Reference Another Sheet" link in the help card
 Select the sheet that has the data you're looking to reference
 Click on the column header for the Checkbox column
 Click the "Insert Reference" button
 This will insert the reference into the formula surrounded in curly braces { }
 After the closed curly brace } type this
 , 1,
 Click the "Reference Another Sheet" link in the help card
 Click on the column header for the Dropdown column
 Click the "Insert Reference" button
 After the newly inserted closed curly brace } type this
 , "Location 1")
 Hit Enter on your keyboard
This will create a formula that looks similar to this:
=COUNTIFS({Referenced Range 1}, 1, {Referenced Range 2}, "Location 1")
More information on creating these types of formulas can be found in the article I linked above.
 On the sheet you'd like to put the formula, start by typing this

Thank you! I will try this.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 183 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!