Dynamic range from which to use =SUMPRODUCT()

[dralkyr]

I need to check if a name falls within a validation range. For that I'm using =SUMPRODUCT(--(D2=NamedRange). Now, I need to make sure that the NamedRange has no blanks, or it screws it all up. But the data change on a frequent basis, which is usually just appending or deleting a name from the range. The range from which names may be appended or deleted is 3 columns and ~30 rows. I tried an array formula of {=INDEX($A$1:$A$10,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10)),ROWS($A$1:$A1)))}, but that returns #NUM ! errors whenever it is too long, and so the SUMPRODUCT() doesn't return any data, only the #NUM ! error. How can I do this? In Google Sheets this would be a breeze, just using =FILTER(range,NOT(ISBLANK(range)), but such a construction doesn't exist in Excel.

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-general/1236186-dynamic-range-from-which-to-use-sumproduct.html & https://www.excelforum.com/excel-ge...med-range-of-non-blank-values-in-a-range.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[dralkyr]

I do not have edit permissions, so I'll just add this here. Sorry I didn't know the cross-posting rule. This is a bit of an emergency at work, so I was reaching out for all the resources I could.