MrExcel.com - Your One Stop for Excel Tips & Solutions

COUNTIF? Or different formula?

Posted by Becky on November 15, 2001 1:47 PM

I am trying to write a formula to look at a column of information and give me the number of items in the column, eliminating duplicates. For example:

Apple
Orange
Banana
Orange
Grape
Strawberry
Pineapple
Grape

There are 8 items in the list, but 6 different fruits. I need to write a formula that would determine that there were 6 different items on the list. This sounds so simple, but I just can't figure it out!

Thanks!!!!
Becky

Posted by Richard S on November 15, 2001 1:50 PM

Pivot Table?

Could you try a pivot table? This would you the count of each type of fruit, and the number of rows in the table would give the number of unique types of fruit.

Richard


Posted by Becky on November 15, 2001 2:04 PM

Re: Pivot Table?

I haven't ever worked with a pivot table, so I am not sure if it would work. Can you give me a quick pivot table lesson?

Thanks
Becky


Posted by Barrie Davidson on November 15, 2001 2:17 PM

Pivot table lesson


Becky, have a look at my website

http://ca.geocities.com/b_davidso/Web_Page_Files/Excel/pivottable1.html

Does this help you out?
BarrieBarrie Davidson

Posted by Becky on November 15, 2001 2:32 PM

Thanks for the lesson, but...

I don't know that this is the best solution to my problem. I have a worksheet that has about 30 different groups of information. Some of the lists are small (4 items) and some are larger (35 items), and I would rather not have 30 pivot tables. I have been using count, and then manually subtracting out duplicates, but as the lists change, I need to remember to recalculate the manual entries (which I don't always remember to do). Do you know of an easier way?

Thanks
Becky


Posted by Aladin Akyurek on November 15, 2001 3:22 PM

Some of the published formulas are:

(1) =SUM(IF(FREQUENCY(MATCH(A1:A8,A1:A8,0),MATCH(A1:A8,A1:A8,0))>0,1)) [ Pearson ]

(2) SUM(1/COUNTIF(A1:A8,A1:A8)) [ Hager ]

The above two (the second is an array formula) cannot have blank cells in the range to which they are applied.

The one that follows, a modification to the second formula, brings up some memories back; anycase it can be applied to a range with blank cells.

(3) =SUM(IF(COUNTIF(A1:A8,A1:A8)=0,"",1/COUNTIF(A1:A8,A1:A8)))

(4) You can also use Advanced Filter to show unique records which you can than count.

The 2nd and 3rd formulas both require that you hit CONTROL+SHIFT+ENTER at the same time to enter them.

Aladin

========


Posted by Becky on November 16, 2001 6:53 AM

Works Perfectly! Thanks Aladin!!!!

Some of the published formulas are: (1) =SUM(IF(FREQUENCY(MATCH(A1:A8,A1:A8,0),MATCH(A1:A8,A1:A8,0))>0,1)) [ Pearson ] (2) SUM(1/COUNTIF(A1:A8,A1:A8)) [ Hager ] The above two (the second is an array formula) cannot have blank cells in the range to which they are applied. The one that follows, a modification to the second formula, brings up some memories back; anycase it can be applied to a range with blank cells. (3) =SUM(IF(COUNTIF(A1:A8,A1:A8)=0,"",1/COUNTIF(A1:A8,A1:A8))) (4) You can also use Advanced Filter to show unique records which you can than count. The 2nd and 3rd formulas both require that you hit CONTROL+SHIFT+ENTER at the same time to enter them. Aladin ======== :