Dynamic range from which to use =SUMPRODUCT()

dralkyr

New Member
Joined
Jun 29, 2018
Messages
7
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.
 

dralkyr

New Member
Joined
Jun 29, 2018
Messages
7
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.
 

Forum statistics

Threads
1,085,588
Messages
5,384,615
Members
401,913
Latest member
chethan av

Some videos you may like

This Week's Hot Topics

Top