Structured Table References in COUNTIFS 1

[RRinTetons]

Can a Structured Table Reference (STR) be used in place of cell references in COUNTIFS formulas?

I've got a coworker's workbook that takes several minutes to recalc and is taking up over a gb of disk. He's got hundreds of COUNTIFS in cells with multiple references that extend to row 1,048,576 in an effort to avoid missing data when it's added to the 20,000 or so rows he's got now. I immediately thought of putting the data in a table and using STR's in the functions, but is not working. I can use them fine in a SUM function, etc., but i'm struggling with them in this specific case.

This formula appears on a tab named 'OUTPUT' in a workbook that also has a sheet named 'Data'.

=COUNTIFS(Data!$A$2:$A$1048576,OUTPUT!$B$16)

I created a table to hold the data (7 columns) and renamed it 'tbl_Data'. One of the columns is named 'Order Year'. The names appear correctly in Name Manager.

If I write =SUM(tbl_Data[Order_Year]) I get the expected return.

If I write =COUNTIFS(tbl_Data[OrderYear],OUTPUT!$B$16) Excel barks at me and returns #VALUE.

What am I missing?

-
Richard Ray
Developer, Data Analyst
Jackson Hole, WY

 

[SkipVought]

Hi,

[tt] =SUM(tbl_Data[Order[highlight #FCE94F]_[/highlight]Year]) I get the expected return. [/tt]

[tt]=COUNTIFS(tbl_Data[OrderYear],OUTPUT!$B$16) Excel barks at me and returns #VALUE.[/tt]

Two different header values!

Please be aware: you don't need to TYPE Structured Table references. One of Excel's great features is intellisense. Type a character in a formula and Excel gives you a list of options matching the character(s) entered, including all Table Headers and Structured Table elements.

So, in your sheet, if you were to enter, in your COUNTIFS first argument, [tt]tbl[/tt], Excel would list every element that begins with [tt]tbl[/tt] and you would be able to Select & TAB to place the selected element correctly into your formula, without error!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[RRinTetons]

My apologies, my forum posts are done via Dragon Naturally Speaking. I missed editing the COUNTIFS example to include the underscore in the name. As my keyboard skills are pretty badly deteriorated< I certainly use Intellisense, as well as other syntax helpers, when writing code or entering formulas.

That aside, I understand you to be giving an authoritative answer that, yes, STR’s do work in COUNTIFS? If so then I’ll search through the workbook further to see where I’m making a mistake.

-
Richard Ray
Developer, Data Analyst
Jackson Hole, WY

 

[combo]

Yes, you can use tables in COUNTIFS criteria_range, if you meant that. It's not straightforward, but natural if you realise that you work with names:
[tt]=COUNTIFS(INDIRECT("tbl_Name[ColumnHeader]"),OUTPUT!$B$16)[/tt]
where tbl_Name and ColumnHeader are table name and column reference you try to count in.

BTW, the same reference (i.e. =INDIRECT("tbl_Name[ColumnHeader]")) can be used in data validation for cell's drop-down list.

combo

 

[SkipVought]

@combo,

Using INDIRECT() in Data > Validation > List where the table is a Structured Table is *genius*! For a decade and a half I've been defining a Named Range to get around this seeming disconnect.

However, why would you ever want to use INDIRECT() for a table range, in any formula. It defeats the great help you get from IntelliSense.

Anyhow, thanks for that tip. It will be a time saver.

BTW, you can enter in an empty cell...
[tt]
=[highlight #FCE94F]tbl_Name[ColumnHeader][/highlight]
[/tt]
...using IntelliSense and COPY the [highlight #FCE94F]correct[/highlight] guts to PASTE into the INDIRECT() in your Data > Validation List formula.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

@SkipVought

Some time ago I made no-VBA excel application, where structured tables are a kind of dictionaries for users. External data is imported into table, the table is extended with additional columns with DV, filled by the user. Next it is processed with additional data in power query.
The user has to update dictionaries, refresh table and fill additional columns with DV if necessary, finally refreshe output table.

And, strange, after reading your post I returned to simple =COUNTIFS(tbl_Name[ColumnHeader],OUTPUT!$B$16) and it worked. I had to misspell previously, so the rest of my solution.

combo

 

[SkipVought]

@combo et al,

Great. It works!

One addition I would also make, since I see the absolute reference for the criterion (second argument), I would routinely make this a Named Range like...
[tt]
=COUNTIFS(tbl_Name[ColumnHeader],SelectedMKT)
[/tt]
Now you begin approaching a Self Documenting application. A meaningful name is so much more helpful than a sheet and range reference.

Oh, yes, on the Output sheet (or wherever) I would put that name in an adjacent cell B15 or A16, for instance, and use Formulas > Defined Names > Create from Selection...and then select Create names from values in the: whatever is appropriate. So the name of the range is also documented in the source data sheet range location.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[RRinTetons]

PROBLEM SOLUTION

Interesting discussions re INDIRECT. To recap my original problem, I had an #VALUE error when attempting to replace cell references with Structured Table References (STR's) in COUNTIFS & SUMIFS. As would be expected, the problem is nothing to do with either STR's or the formulas I was working on. The actual formulas contained multiple criteria in one formula:

=COUNTIFS(Data!$A$2:$A$1048576,OUTPUT!$B$16,Data!$B$2:$B$1048576,"<="&OUTPUT!$C$7,Data!$C$2:$C$1048576,OUTPUT!H$16,Data!$H$2:$H$1048576,OUTPUT!$B17)

Note the range reference going down to 1048576. As this formula appears over a thousand times in this workbook it's no surprise breakout times were awful. The user has only about 15,000 rows of data, it will grow much beyond 100,000 rows of data, but he was worried about his formulas not including added rows of data later on. Seem like a job for a Table and STR's.

I attempted to sneak up on that by only replacing the first criteria set with my STR's. I painstakingly went through spelling and punctuation but continued to get the #VALUE error. Notably it was not a#NAME error. I went back and reread the docco and applied it to the formulas on a sentence by sentence basis. When I got to the sentence that included the information that all criteria ranges had to have the same depth a light went on! So now it looks like this:

=COUNTIFS(TheData[Order Year],OUTPUT!$B$16,TheData[Order Date],"<="&OUTPUT!$C$7,TheData[Arrival Date],OUTPUT!C$16,TheData[Location],OUTPUT!$B17)

More work is needed to replace the single cell references and retain portability, but the answer to the original question on using STR's in these types of functions, is that yes of course you can.

-
Richard Ray
Developer, Data Analyst
Jackson Hole, WY

 

[SkipVought]

FYI,

I never use COUNTIFS(). Never.

I much prefer SUMPRODUCT as it is so much more intuitive to understand and code. For instance in your latest formula, here's what it would look like...
[tt]
=SUMPRODUCT((TheData[Order Year]=OUTPUT!$B$16)*(TheData[Order Date]<=OUTPUT!$C$7)*(TheData[Arrival Date]=OUTPUT!C$16)*(TheData[Location]=OUTPUT!$B17))[/tt]

Notice that each expression, within a set of parentheses, is a mathematical equality requiring no additional QUOTES or CONCATENATIONS.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein