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 (check-in 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
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!