» Filtering a List into Unique Records Using the COUNTIF Formula

this formula with iF AND COUNTIF is excelent. The formulas: =A1=A2 in the same table, select FALSE in the criteria list of filter. =if(A1<>A2,0,1) is functional too select 0 in the criteria list NOTE. ordered data

Use the Advanced Filter and click on 'Unique Records Only'. Much quicker!!

Just use COUNTIF then set the filter to 1

I NEED TO FIND THE DUPLICATE VALUES IN 3 COLUMNS AND JUST DISPLAY DUPICATE IN ANOTHER COLUMN. HOW DO I DO DO THAT

I am trying to count the number of unique entries in a column based on other field in the adjcent rows

Hi jonny,

If A1:A99 contains a list of items, some of which are duplicates, then enter the following formula in B1 and copy down to the end of the list:

=COUNTIF(A$1:$A99,A1)

This will return a value that tells you how many times the entry in column A appears in the list.

If you want to go further and actually display that duplicate enter beside itself, but show nothing for entries that only occur once, you could expand the formula to:

=IF(COUNTIF(A$1:A$4,A1)=1,"",A1)

That will show only the entries that appears more than once.

If you want to expand the range from a single column (A) to a list / block of items across, say, three columns (A1:C99), then in column D, enter:

=IF(COUNTIF($A$1:$C$99,A1)=1,"",A1)

Copy this across to E and F, and down to the bottom of the list, and the formulae will give you items that appear in the block of data more than once.

Not sure if that is what you mean, but hopefully one or more of the above will allow you to extrapolate to get what you actually need.

Alan.

Hi Sean,

I don't really understand what you mean.

The formulae immediately above in my reply to 'jonny' will give you a way to identify unique entries (count of 1), and if you dropped that '1' into column B, and then summed column B it would give a count of total unique entries.

However, I don't understand what you mean by, "based on other field in the adjcent rows".

Please can you elaborate or give a short example of the data and result you want?

Thanks,

Alan.

I need to count the number of times that the dates from Aug 1, 2003 THROUGH Aug 15, 2003 occur in a column. Is this possible?

Hi Scott,

Lets assumed you have A1:A4 containing:

7 Jul 2003
3 Aug 2003
13 Aug 2003
16 Aug 2003

The following formula will return the value '2':

{=SUM(((A1:A4)>=VALUE("1 Aug 2003"))*((A1:A4)
Note that this is an array formula, so you must enter it (without the curly brackets), with Shift-Ctrl-Enter.

Check Excel's help for more on array formulae.

Does that work?

Alan.

Looks like the forumula got cut off when I pasted it. Should read:

{=SUM(((A1:A4)>=VALUE("1 Aug 2003"))*((A1:A4)
Sorry!

Alan.

Not sure what is going wrong - I cannot seem to get the whole formula in.

Ah well, try again:

{=SUM(((A1:A4)>=VALUE("1 Aug 2003"))*((A1:A4)
If that looks the same as above, then the following should end the formula:


If that doesn't work, then PM me from the forums section.

Alan.

i am trying to sum certain numbers in one column based on data from another column. for instance:
column1 column2
y 20
n 30
y 40
n 50

i want to be able to sum the y's and sum the n's..thanks

Hi Casey,

If that data is in A1:B4, then:

{=SUM((A1:A4="y")*(B1:B4))} equals 60
{=SUM((A1:A4="n")*(B1:B4))} equals 80

Note that these are array formulae, and must be entered (without the curly brackets), with Shift-Ctrl-Enter.

Check the Excel help for more on arrays.

Alan.

Sorry Alan

Your solution, corrected by Excel, gives me a #VALUE! error. I used {=SUM(((A2:A182)>=VALUE("1 Aug 2003"))*((A1:A4)))} Is there more to the formula? I noticed you used >=VALUE in your formula. I'm trying to delimit the date range from 1 Aug 2003 through 15 Aug 2003. Shouldn't <=VALUE be included somewhere as well? Also, I'm using the 08/01/03 date format. Does that make any difference?

Oooops! I noticed an error in the formula and thought I had it fixed by using {=SUM(((A2:A182)>=VALUE("1 Aug 2003"))*((A2:A182)))}
It returned 6850537! This, of course, is incorrect. Even if every date in that column were between 1 Aug through 15 Aug, it cannot be any higher than 181, since I'm only using 181 cells. (Manually counting, the correct answer should be 101.) Help!

Hi Scott,

Why can't that be right?

If you hve only two 'y's and tey had, say, 6850000 and 537 beside them, then you'd get that answer (or example).

Do you mean you want to count the NUMBER of 'y's? If so, just use COUNTIF.

Alan.

Hi Alan--

Thanks for all your help thus far. I'm sure you have other things to do besides answering my silly little questions. As I stated in my original post from Aug 15, "I need to count the number of times that the dates from Aug 1, 2003 THROUGH Aug 15, 2003 occur in a column." To answer your question above, no, I'm not counting "y"s. I need to count the NUMBER of dates from 08/01/03 to and including 08/15/03. COUNTIF works beautifully if I only want to start counting from 1 Aug 2003 and include every date on up. For instance, if I use =COUNTIF(A2:A182,">=1 AUG 2003") I get 181 returned. However, the delimiting part <=15 AUG 2003 is still a mystery. Where does that go in the equation? Do you know? I've tried various versions of nested functions, but I keep getting the "Too many arguments" error message. Ugh!

Hi Scott,

I think that formula up above would solve your problems, but the board consistently cuts off the formula for some reason.

Same offer is still open as per my reply (August 15, 2003 9:58 PM EST).

Alan.

Hi Alan--

Your reply (August 15, 2003 9:58 PM EST) is incorrect. Thanks for your time.

Does anybody else have the correct answer?

Hi Scott,

I tested it again and it definately works.

Are you sure you are entering the full formula - not just what appears above (note my repeated comments regarding the missing elements that the ExcelTip board refuses to post)?

I'll try again here, but I am not hopeful:

{=SUM(((A1:A4)>=VALUE("1 Aug 2003"))*
((A1:A4)
If that appears correctly, it should contain 69 characters from the opening curly bracket to the closing one inclusive.

If you have entered the formula *fully*, and it still doesn't work, you might want to read the Excel help file on array formulae to see if you are entering it correctly.

If that doesn't appear correctly, I am giving up and someone else can help Scott with the 'correct' answer.

My offer is withdrawn - please do not PM me.

Alan.

Hi Alan--

Brilliant! With a few minor adjustments, the following works correctly:

{=SUM(((A2:A45)>=VALUE("1 Aug 2003"))*((A2:A45)<=VALUE("16 Aug 2003")))}

Thank you thank you thank you!

This interface keeps reversing the "less than or equal to" (<=) sign. That's odd.

{=SUM(((A2:A45)>=VALUE("1 Aug 2003"))*((A2:A45)<=VALUE("15 Aug 2003")))}

Note that if you use less than or equal to for the later date boundry, then it will include that date (depends on what you want exactly).

The issue is with the signs. The board interprets them as HTML code. I think the IRUBIN conslutants are looking at it.

Alan.

I am trying to create a spreadsheet which contains names of people on column C. However, as I enter names into the cells in column C, I would like excel to give me an error if the name being entered already exists in another cell in Column C. Anyone know how to do that?

Hi Jorge,

You don't say how you would like to be warned, so I will assumed that Column B is empted, and we will make the cell beside your new entry in Column C change to "Repeat!" if it is so:

Assuming your list starts in C1 and goes down, enter the following in B1, and copy down as you enter new names:

=IF(COUNTIF(C$1:C1,C1)=1,"","Repeat!")

Notes:

If you copy it past the end of your list, into a blank part of the sheet, it will flag repeats of the blank cells, so you might want to error trap if you need to go that way.

This will only work if you enter the names with exactly the same spelling and form of course.

Alan.

I have an athletic spreadsheet displaying the name and result of a particular match. I want to be able to Auto Filter the names to easily read the results. That's easy...

Now I want to also be able to count the "Wins" and "Losses" of that particular athlete without adding up all of the others stats.

The Auto Filter just simply "hides" the others names when I choose a particular athlete so when I do a COUNTIF, it counts every name rather than the filtered name. Is there a secret COUNTIF IF statement that I can embed?

Hi Rmolitor,

COUNTIF is really just a fudge functon that tries to make it easy for people to build an array formula withouth knowing anything about what an array formula is.

As soon as you get into a situation where you need to use multiple criteria, you only have two options that I can see:

1) Create some ugly new field that people often call something like a "unique identifier" - normally by concatenating other fields.

This is both ugly and unecessary.

2) Use and array formula to multiply (effectively create AND operators) across multiple fields:

Example Data:

Alan Red 1
Alan Blue 2
Bob Blue 3
Bob Red 4

The following formula returns the value '2':

{=SUM((A1:A4="Alan")*(B1:B4="Blue")*(C1:C4))}

This has to be entered, without the braces, using Shift-Ctrl-Enter.

Does that solve your problem?

Alan.

On the right track, but it's not right yet.

Let me be more specific to make it easier. There will be multiple entries for any single athlete, but the result will obviously change depending on success.
Names are in column B and Results are in Column C
John Smith W
Jack Johnson L
Pete Anderson W
John Smith L
John Smith W
Pete Anderson L

I want to add up the records in Cell A2 (with a CONCATENATE command) by a dropdown list of athletes names that I've created with everyone's name (spelled correctly) in Cell A1.

Cell A1 will have a dropdown of
John Smith
Jack Johnson
Pete Anderson
etc...

Cell A2 will have
Wins 2 (or whatever)

Cell A3 will have
Losses 2 (copy of A2 referencing losses)

I really appreciate all of your help Alan. If you would also email me directly at ryan_molitor@hotmail.com, that would also help considerably...

Hi Ryan,

You should still be able to use that same basic formula, but with a slight tweak.

If columns B and C contain the data you gave, and A1 contains, the name of the athlete, then the following should do it:


{=SUM(((B1:B6)=A1)*((C1:C6)="W"))}

A3 might then have:

{=SUM(((B1:B6)=A1)*((C1:C6)="L"))}

If you want to display the text as you outlined above you can do that by either concatenation or using a custom format.

Does that do it?

Alan.

Alan,

You are a life saver (no, not the candy).

That did it (with a little tweaking for exactly what I wanted)!

Thanks.

Ryan