COUNTIFS Range with multiple columns?

James Vandezande

Can I use a range with multiple columns in a COUNTIFS statement? I'm trying to setup a Summary Metrics sheet that can use one formula to summarize data from a skills registry table. The source table looks like this:

And our current summary table is set up like this:

Right now, we created a referenced range for each individual column in the roster. But now that we want to create different summaries for "Silo" rows that contain "Architect" and other terms, that's a lot of cells to edit. So, I'm wondering if COUNTIFS can accept a single reference range of ALL the columns and then use the @row value (such as Revit, Dynamo, etc.) to inform the COUNTIFS statement.

David Joyeuse

Hi @Jamesvan5

It took me quite some time to understand what you were trying to do here. If I got it right, you want to have only one big reference Range, and use a value to tell smartsheet which column you want to count.

Sadly, I don't think this is possible right now, as Smartsheet sees range as one big column most of the time, and not different columns.

I'm not even sure that having your columns name on your first row would help in any way.

So I fear you're up for a bit for a lot of edit.

Ben Goldblatt

Hi @Jamesvan5,

While it is possible to reference multiple columns in a single cross-sheet reference range, for what you're looking to accomplish I think this would require some Helper columns with the reference values all listed out on rows to be able to fully utilize the @row values. I highly recommend Submitting a Product Enhancement Request to have our Product Team look into some possible options for implementing the reference range you're looking to create and utilize.

More information on reference ranges from other sheets can be found in the Cross-sheet formulas Help article.

Thanks,

Ben