How to deal with blank cells to get unique distinct alphabetically sorted list?

I have the formula to get unique distinct alphabetically sorted list using,

{=IFERROR(INDEX(Agent,MATCH(MIN(IF(COUNTIF($I$3:I3,Agent)=0,1,MAX((COUNTIF(Agent,"<"&Agent)+1)*2))*(COUNTIF(Agent,"<"&Agent)+1)),COUNTIF(Agent,"<"&Agent)+1, 0)), "")}

Agent = $B$4:$B$103

When my named range, Agent have blank cells the above formula doesn't work.

How to deal with blank cells and help me out!

Thanks
Karthikeyan T

Try appending a "" to Agent ... Agent&"".

Edit: that only applies to the Criteria argument and not the Range argument of COUNTIF.

Thanks for the reply FlameRetired!

I have updated the formula as you suggeseted, but no result .

How to achieve this without head ache????

It sounds like it is something else.

It is usually easier and faster to get solution if you upload an Excel file that represents what you are working with.

If you are not familiar with how to do this

• click FAQ at the top of this page,
• under Board FAQ click Reading and posting messages
• then click Attachments and images
• You will find instructions on how to do this.

I have attached my working file please go through it and suggest a solution.

I want to summarize the Agents worked hour in column I.

The formula in column I working only if column B (Named Range: Agent, refers to: $B$3:$B$102) is filled with values and not contain a single blank cell.

The formula needs to work with blank cells.

Help me out in this.

Production Summary.xls

This array-entered formula in I3 and filled down will work with blank cells, return unique text and sorted in alphabetical order.

Formula:

Please Login or Register  to view this content.

Wow!!! Amazing!!!

It worked for me. Thank you so much FlameRetired!!!

You are welcome. Glad to help.