Excel COUNTIF function problem

[andyp161]

Hi there,

Is it possible to adapt the COUNTIF function so that duplications ar
only counted once. For example, if I had a list as follows: "ANDREW
DAVID, SIMON, JOHN, ANDREW, ANDREW" I would want the COUNTIF functio
to return 1 for ANDREW rather than 3.

Many thank

 

[Frank]

Hi
see:
http://www.cpearson.com/excel/duplicat.htm#CountingUnique

 

[Gary Thomson]

Andy,

It depends what you are really wanting the function to do.

If you just want it to display 1 if Andrew is in the list
and zero if he is not, then you could do a simple:

=IF(ISERROR(VLOOKUP("Andrew",$A$1:$A$10,1,FALSE))
=FALSE,1,0)

where A1:A10 is your list of names.

If not, please be more specific and I'll do my best to
give you an answer,

Regards,

Gary.

 

[David Burr]

It's a bit sloppy but if you want a quick fix you can
create a new column that references your first column and
contains the following formula to screen out the
duplicates. Then you can use the standard countif to count
the names in the new column.

For example

Column A has the names in it starting from row 1
Column B has this formula in it starting from row 1:
=IF(COUNTIF(A$1:A1,A1)>1,"",A1)

If you copy this down to the end of the column the ending
row will increment but the starting row will remain at 1.
When the name is found a second time it will be replaced
in the new column by "".

However, it seems to me that CountIf is probably not the
best tool to use here. What is your actual problem? If you
just want to test whether a given name exists in a range I
would do it using VLookup which will return N/A if its not
there. I would then wrap the VLookup in an ISNA function
which will return True if VLookup return N/A. In
otherwords the ISNA will say true if the name is not in
the list. If you want to test for the presence of a name I
would wrap that in a NOT function reversing the value. You
would then get True if the name is in the list or False if
it isn't.

 

[andyp161]

Many thanks for the feedback. I was trying to simplify my problem but
in doing so, I have not expressed myself particularly well
apologies.

The actual problem is as follows. I am trying to create an M
spreadsheet that records the number of corporate brochures consultant
request for each company they visit. The MI spreadsheet is to draw it
information from a main table which is updated in chronological order (
new row is inserted for each separate request) to record in Column A th
date of the request, in Column B the name of the consultant, in Column
the company the consultant wants the brochure for, and in Column D th
quantity of brochures requested.

The MI spreadsheet is set up with every possible consultants nam
running down Column A: in Column B I want to input a formula that wil
draw from the main table the TOTAL number of DIFFERENT companies th
consultant has visited, and in Column C the TOTAL quantity of brochure
the consultant has requested.

Therefore, if Andrew visited company AA on 1/09/04 and requested
brochures, and then visited the same company again on 10/09/0
requesting a further 3 brochures, I want the MI spreadsheet to sho
that Andrew has requested a total of 8 brochures and has visited
total of 1 company.

Currently, to count the total quantity of brochures requested by eac
consultant I am using the following:

SUMIF($B$2:$B$14,"David Michaels",$D$2:$D$14) (please note that th
refered to MI spreadsheet is actually a table set below the mai
table)

...AND THIS WORKS JUST FINE.

However, I do not know how to count the total number of DIFFEREN
companies visisted by each consultant.

Many thank

 

[Bob Phillips]

Andy,

This will count the number of unique entries in column A

=SUM(1/COUNTIF(A1:OFFSET(A1,,,COUNTA(A:A)),A1:OFFSET(A1,,,COUNTA(A:A))))

which is an array formula, so commit with Ctrl-Shift-Enter

If you also want to include a condition then use

=SUMPRODUCT((Sheet1!A1:OFFSET(A1,,,COUNTA(A:A))<>"")/COUNTIF(Sheet1!A1:OFFSE
T(A1,,,COUNTA(A:A)),A1:A10&""),--(Sheet1!B1:OFFSET(B1,,,COUNTA(A:A))="Andrew
"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[David Burr]

I understand what you want to do and you can do it with
functions and I can help you with that. However, have you
looked at Pivot Tables? They are complicated at first but
they are very powerful and I think they're the obvious
answer to your problem. Have a look at them and if they
aren't suitable I will help you through your problem.

 

[Bob Phillips]

This bit

=SUMPRODUCT((Sheet1!A1:OFFSET(A1,,,COUNTA(A:A))<>"")/COUNTIF(Sheet1!A1:OFFSE
T(A1,,,COUNTA(A:A)),A1:A10&""),--(Sheet1!B1:OFFSET(B1,,,COUNTA(A:A))="Andrew
"))

is contiguous, so should read

=SUMPRODUCT((Sheet1!A1:OFFSET(A1,,,COUNTA(A:A))<>"")/COUNTIF(Sheet1!A1:OFFSE
T(A1,,,COUNTA(A:A)),A1:A10&""),--(Sheet1!B1:OFFSET(B1,,,COUNTA(A:A))="Andrew
"))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Andy,

This will count the number of unique entries in column A

=SUM(1/COUNTIF(A1:OFFSET(A1,,,COUNTA(A:A)),A1:OFFSET(A1,,,COUNTA(A:A))))

which is an array formula, so commit with Ctrl-Shift-Enter

If you also want to include a condition then use

T(A1,,,COUNTA(A:A)),A1:A10&""),--(Sheet1!B1:OFFSET(B1,,,COUNTA(A:A))="Andrew
"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[andyp161]

Many thanks for the feedback.

Bob Phillips, unfortunately I cannot seem to get your formula to work
is it designed to count unique entries in Column C in relation to th
name of the consultant in Column B i.e. if B2=Andrew and C3=Microsoft
and, B8=Andrew and C8=Microsoft, I want the formula to tell me tha
Andrew has visited a total of 1 company.

David Burr, I understand how to use pivot tables but I would reall
like to solve this problem using functions so that the MI is update
the moment someone inputs a new entry in the main table.

Many thank

 

[Bob Phillips]

Andy,

My mistake. I tried to make it dynamic so that as you added entries at the
bottom, it would still work. But I missed one entry, worked on my data as I
had exactly 10 rows.

Try this version. With your data it returned 1. I then added Andrew in B9,
Oracle in C9 and got 2.

=SUMPRODUCT((Sheet1!C1:OFFSET(C1,,,COUNTA(C:C))<>"")/COUNTIF(Sheet1!C1:OFFSE
T(C1,,,COUNTA(C:C)),C1:OFFSET(C1,,,COUNTA(C:C))&""),--(Sheet1!B1:OFFSET(B1,,
,COUNTA(C:C))="Andrew"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)