Excel Cells Populated with Text, Count Only Once with Blanks 3

[akrshaw]

I have tried several solutions to resolve this issue, which are not working.

I have the below:

Bid MGR
Fred
Jane

Mike
Jane

Greg
Fred

I need to have this cell count the number of individual Bid MGRs, ignoring the blanks and duplicates.

Thanks

 

[akrshaw]

Thanks everyone for all the help!~

I have tried both

xlbo

=SUM(IF(FREQUENCY(MATCH(IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),0),MATCH(IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),0))>0,1))-1

N1GHTEYES

SUM(IF(FREQUENCY(MATCH(IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),0),MATCH(IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),0))>0,1))-IF(SUM(IF(Bid_Mgr="",1))>0,1,0)

Both are giving me #VALUE!, I am not sure what I am doing wrong?

 

[xlbo]

Do you have any error cells in your range? #N/A or #VALUE! or DIV/0! ?

formula worked fine on my test data set which I just copied from your OP

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[N1GHTEYES]

Did you enter it as an array formula? In other words, having typed the entry into the cell, do not hit "enter", instead hit Ctrl Shift Enter simultaneously.

Did you also remember to name the appropriate range Bid_Mgr?

Tony

 

[akrshaw]

xlbo - No error cells in the range.

N1GHTEYES - I do have the range named Bid_MGR, did the CTRL - Shift - Enter at the same time...though not sure how well I am hitting them at the same time.

Still getting the Value error....

 

[N1GHTEYES]

I tried it with the following list:
fred
jim
harry
bill
fred
jim

and with:

fred
jim
harry

bill
fred
jim

In both cases it gave the answer 4 - which is correct.

Do this:

Create a new workbook.

Enter the first list above into cells A1:A6.

Select this area

Name the area Bid_Mgr. You can do this directly by typing it into the name entry box (top left), or by using the menu.

Copy this formula:
=SUM(IF(FREQUENCY(MATCH(IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),0),MATCH(IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),0))>0,1))-IF(SUM(IF(Bid_Mgr="",1))>0,1,0)

Click on cell B1.

Click in the formula entry area (the white text-entry space to the right of the fx symbol above the working area of the application)

Paste the formula into the formula entry bar.

Press and hold ctrl
Press and hold Shift
Press enter and let go
let go of shift & ctrl.

Write back and tell us what happens

Tony

 

[akrshaw]

N1GHTEYES

It worked...followed the same direction in the original file and it worked!~

Thanks so much, it was my first time to use an array formula!~

 

[N1GHTEYES]

You're welcome, but it was really Skip's formula.

Now you've got the hang of them I'm sure you'll find uses for them all the time - I do.

Tony

 

[xlbo]

I would err against using array formulae unless really necessary - they are very memory intensive

the requirement to use one is often the sign of a workbook that needs a re-design! enerally, if you set out your wrkbook as you would a set of database tables, it is very rare that you actually need an array formula - certaily notmore than a couple - most things you can do with them can be replicted using a pivot table

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question