quickie

mark.wagner

New Member
can one use a named range in an array formula?

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sure - have a problem?

First, I have this array formula. works great every time.

=SUM(IF(count_type=IL_MDU,IF(count_market=IL_MKT,IF(count_date=Sep,count_units))))

Then I have this array formula. same basic formula, uses slightly different ranges and is based on cell references. works great every time.

=SUM(IF(MW_data!\$V\$2:\$V\$5525=Sep,IF(MW_data!\$T\$2:\$T\$5523=\$B\$70,IF(MW_data!\$A\$2:\$A\$5523=\$A\$4,MW_data!\$B\$2:\$B\$5525))))

I created the range MW_date as:

=OFFSET(MW_data!\$V\$2,0,0,COUNTA(MW_data!\$V:\$V),1)

incidentally, this is the same setup for the named range count_date in the first example. so I know it works.

However, substituting the range name into the formula results in #NA.

=SUM(IF(MW_date=Sep,IF(MW_data!\$T\$2:\$T\$5523=\$B\$70,IF(MW_data!\$A\$2:\$A\$5523=\$A\$4,MW_data!\$B\$2:\$B\$5525))))

I even tried setting up named ranges for all the parameters of the array formula, thinking that having different range sizes might be the difference (even though this was not an issue with the first formula) . no luck.

so, can anyone tell me what's wrong with this formula when the named range is added?

thanks to all.

All the ranges must be the same size.

When, in your 3rd formula, you substituted some of the cell refs with names, I'll bet they no longer are the same size.

Replies
2
Views
205
Replies
1
Views
133
Replies
9
Views
232
Replies
5
Views
178
Replies
23
Views
353

1,203,625
Messages
6,056,393
Members
444,862
Latest member
more_resource23

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.

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

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