How do I select a column from a cross sheet reference range?
I need data from another sheet. I am counting the number of nonzero 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 columns16}, >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:
Nonzero rows for different sheet column 1 = 14
Nonzero 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 12}
Is there a way to craft a formulas like this:
=COUNTIF({ref dif sheet col 12}[COLUMN 1],>0)
=COUNTIF({ref dif sheet col 12}[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 nonzero in a column" as a Sheet Summary Field. I started down that path but didn't finish. Currently, I am still using one crosssheet reference per column.

Thanks for the feedback
Help Article Resources
Categories
Check out the Formula Handbook template!