Countifs(), Indirect(), and dynamic named ranges

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
419
Hi all,

I have run into a little conundrum.

I have a large data table (>430K records) in which I have defined multiple named ranges--each range being a single column. The named ranges are all dynamic.

There is also one worksheet called "Reference" that summarizes some of the main geographic levels such as City, County, etc. This worksheet is basically a sheet of lists with a count of records for each member of the list.

The workbook is designed to be point-and-click using drop down lists so the user does not have to think much.

From there, I have four summary worksheets: Statewide, County, City and Branch. Each of these worksheets has multiple formulas, all SUMIFS() and COUNTIFS(), that summarize from my data sheet and reference sheet in various ways.

The reason I am posting is because my formulas work fine with just one named range, but fail with more than one. So, by example, this works:

Code:
=COUNTIFS(Data!$Q$2:$Q$463777,INDIRECT("Reference!D"&$C$1+1)-6000,EstIncome,A17)

But this will not work:

Code:
=COUNTIFS(Counties,INDIRECT("Reference!D"&$C$1+1)-6000,EstIncome,A17)

Even though "Counties" is a named range that represents the exact same range as "Data!$Q$2:$Q$463777".

Consistently, if I put a named range in for the INDIRECT() filter criteria, the formula fails.

Is there some sort of restriction in Excel against using more than one named range in a formula or something? What am I doing wrong?

I am hoping it's something I am doing wrong because I do not want to have to manually edit 500 formulas every time I run this report.

TIA
Al
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
It works OK for me, what error do you get?

are you sure that Counties is the same size as EstIncome, how are they defined?
 

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
419
Barry,

Thanks for the quick response.Looking at the dynamic range definitions I have:

Code:
=OFFSET(Data!$A$2,0,19,COUNTA(Data!$A:$A)-1,1)

for the "estIncome" range and

Code:
=OFFSET(Data!$Q$2,0,0,COUNTA(Data!$Q:$Q),1)

for "Counties".

I am sure you will notice the first is using a column offset and the second does not. That's because column A will always have a value, whereas Column T will not. So I chose an offset. In the second one, column Q always has a value because it is decided for each record at the time of compilation in the database. So theoretically they should both return a range of the same length.

HOWEVER, you bring up an interesting fact that I had not considered and will dig into--Could the difference in dynamic range definitions account for the #VALUE! error? I will get back to you on that.

Ok, I'm back.:)

Changing the "Counties" range definition to a mirror of the estIncome definition, i.e:

Code:
=OFFSET(Data!$A$2,0,16,COUNTA(Data!$Q:$Q)-1,1)

has solved the problem!

That pesky "-1" difference was staring at me the whole time and I just could not see it.

Thanks Barry!
 
Last edited:

Forum statistics

Threads
1,141,016
Messages
5,703,751
Members
421,312
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top