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.