×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Structured Table References in COUNTIFS

Structured Table References in COUNTIFS

Structured Table References in COUNTIFS

(OP)
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

RE: Structured Table References in COUNTIFS

Hi,

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

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

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, tbl, Excel would list every element that begins with tbl and you would be able to Select & TAB to place the selected element correctly into your formula, without error!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Structured Table References in COUNTIFS

(OP)
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

RE: Structured Table References in COUNTIFS

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:
=COUNTIFS(INDIRECT("tbl_Name[ColumnHeader]"),OUTPUT!$B$16)
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

RE: Structured Table References in COUNTIFS

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

=tbl_Name[ColumnHeader]

...using IntelliSense and COPY the correct guts to PASTE into the INDIRECT() in your Data > Validation List formula.


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Structured Table References in COUNTIFS

@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

RE: Structured Table References in COUNTIFS

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

=COUNTIFS(tbl_Name[ColumnHeader],SelectedMKT)

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Structured Table References in COUNTIFS

(OP)
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:

CODE -->

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

CODE -->

=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

RE: Structured Table References in COUNTIFS

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

=SUMPRODUCT((TheData[Order Year]=OUTPUT!$B$16)*(TheData[Order Date]<=OUTPUT!$C$7)*(TheData[Arrival Date]=OUTPUT!C$16)*(TheData[Location]=OUTPUT!$B17))


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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close