How do I select a column from a cross sheet reference range?
I need data from another sheet. I am counting the number of non-zero instances in 6 different columns. Currently, I have created 6 different cross sheet reference ranges, one for each column. This works but it took a while to get it all setup. COUNT({reference range sales 1},>0); COUNT({reference range sales 2},>0) etc.
In the source sheet all of the columns are adjacent. Is there a way to select ALL of the columns as a range and then specify to count a specific column in the range using the formula? I have tried using this format but it failed. COUNT({reference range 1}[sales 1]:[sales 1], >0)
Any help is appreciated. I feel like I am missing some simple syntax issue.
Best Answer
-
Hey Chad
Sorry I misunderstood. No, I cannot think of anything you can do that doesn't result in six different formulas- or however many columns you have. The COUNTIF/COUNTIFS is the simplest I can think - it should have been straight forward to replace the range each time with a new range to create the new formula.
Sorry I don't have better news.
Kelly
Answers
-
Hey @Chad Miars
A Function change should get you what you need.
Because all of your columns are adjacent you can use one range. You will need to create the range that encompasses the block of all 6 columns. *As a good practice, consider renaming the smartsheet generically named range to reference the actual column names you are using. This will help you better understand and troubleshoot your formulas.
=COUNTIFS({reference sheet columns1-6}, >0)
The COUNTIFS has the syntax of COUNTIFS(range1, criteria1, range2, criteria2, range3, criteria3, etc). You can have any number of criteria - from 1 to whatever but you must always enter these as a range, criteria pair.
Does this work for you?
Kelly
-
@Kelly Moore Thank you for your response. I see now that my question lacked some detail, and I might have misrepresented the issue.
I am using a =COUNTIF() forumula (not a COUNT as I stated in the question)
My goal is to have a "metrics" sheet where I summarize data like this:
Non-zero rows for different sheet column 1 = 14
Non-zero rows for different sheet column 2 = 35
etc...
If I have a cross sheet reference range that includes columns 1 and 2 from the different sheet, lets call it {ref dif sheet col 1-2}
Is there a way to craft a formulas like this:
=COUNTIF({ref dif sheet col 1-2}[COLUMN 1],>0)
=COUNTIF({ref dif sheet col 1-2}[COLUMN 2],>0)
Or should I just specify different references?
Does using the COUNTIFS() solve this in a different way?
-
Hey Chad
Sorry I misunderstood. No, I cannot think of anything you can do that doesn't result in six different formulas- or however many columns you have. The COUNTIF/COUNTIFS is the simplest I can think - it should have been straight forward to replace the range each time with a new range to create the new formula.
Sorry I don't have better news.
Kelly
-
Hi Chad, did you find a solution? I'd like to select one column from a reference too.
thanks
Laetitia
-
@Balarac I didn't find any special syntax. Someone did recommend that I create the "Count non-zero in a column" as a Sheet Summary Field. I started down that path but didn't finish. Currently, I am still using one cross-sheet reference per column.
-
Thanks for the feedback
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 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!