Cross sheet formula - help

tcanelli41471

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.

Robert S.

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:

1. On the sheet you'd like to put the formula, start by typing this
   • =COUNTIFS(
2. Click the "Reference Another Sheet" link in the help card
3. Select the sheet that has the data you're looking to reference
4. Click on the column header for the Checkbox column
5. Click the "Insert Reference" button
   • This will insert the reference into the formula surrounded in curly braces { }
6. After the closed curly brace } type this
   • , 1,
7. Click the "Reference Another Sheet" link in the help card
8. Click on the column header for the Dropdown column
9. Click the "Insert Reference" button
10. After the newly inserted closed curly brace } type this
    • , "Location 1")
11. 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.