Count Function

R

Roberta

I have a list of 6,000 records. what I'd like to do is count the number of
Contractors in the list. Thr problem is some contractors have more than 1
record and their contractor number shows up as if they are duplicates. I
only want it to be counted once even if they have more than one record.
Here's a sample of the file:

Contractor Truck Owner No. of Units
017324029 542453 EDWARD F BRADY 1
017526830 410486 DIONNE, HEATHER 1
018367773 530126 CLIFFORD DAVIS 3
018367773 530128 CLIFFORD DAVIS 3
018367773 531679 CLIFFORD DAVIS 3
018405052 438438 CIOFFI STEPHEN MICHAEL 1
018440237 537724 JEFF BUZZANCO 1
018788339 521444 BOGDAN HRYC 1
 
D

Domenic

Try the following array formula, enetered using CONTROL+SHIFT+ENTER...

=SUM(IF(A2:A9<>"",1/COUNTIF(A2:A9,A2:A9)))

Hope this helps
 
R

Roberta

I copied this formula in the cell and got an error. When you say
CONTROL+SHIFT+ENTER the status bar just says ready.. what is the
CONTROL+SHIFT+ENTER suppose to do? I changed the formula to A2:A100 in the
formuila
 
A

Aladin Akyurek

Type the formula in the target cell:

=SUM(IF(A2:A100<>"",1/COUNTIF(A2:A100,A2:A100)))

Since this is a so-called array formula, you need to confirm it usin
control, shift, and enter keys at the same time.

An alternative that doesn't require these 3 key combonations is:

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))

I copied this formula in the cell and got an error. When you say
CONTROL+SHIFT+ENTER the status bar just says ready.. what is the
CONTROL+SHIFT+ENTER suppose to do? I changed the formula to A2:A100 i
the
formuila
Click to expand...
 
D

Domenic

When you enter the formula, instead of just pressing ENTER press th
CONTROL+SHIFT keys together and then, while you have them pressed, hi
the ENTER key. If you've done it correctly, Excel will place braces {
around the formula to indicate that it's an array formula.
I copied this formula in the cell and got an error. When you say
CONTROL+SHIFT+ENTER the status bar just says ready.. what is the
CONTROL+SHIFT+ENTER suppose to do? I changed the formula to A2:A100 i
the
formuila
Click to expand...
 
F

Frank Kabel

Hi
instead of committing the formula with a single ENTER use these three
keys in combination
 
Top