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.

Find more posts tagged with

Accepted answers

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

All comments

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

Kbrow

Hello @Chad Miars, I don't know if this has been resolved elsewhere or if you've already solved this yourself, but I found a solution by nesting the INDEX( function when using a range of cross sheet references.

So for the example here

=COUNTIF({ref dif sheet col 1-2}[COLUMN 1],>0)

You would use:

=COUNTIF(INDEX({ref dif sheet col 1-2}, 0, 1),>0)
and

=COUNTIF(INDEX({ref dif sheet col 1-2}, 0, 2),>0)
for the second column.

I hope this helps as much as it helped me.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.