How do I select a column from a cross sheet reference range?

Chad Miars

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.

Kelly Moore

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

Kelly Moore

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

Chad Miars

@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?

Kelly Moore

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

Balarac

Hi Chad, did you find a solution? I'd like to select one column from a reference too.

thanks

Laetitia

Chad Miars

@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.

Balarac

Thanks for the feedback