quickie

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,532
Messages
6,191,607
Members
453,667
Latest member
JoeH7745

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
Back
Top