Counting Multiple of Similar Items as 1

**aviavy** · 04-26-2008, 01:52 PM

Hi,

Here's my problem.
I have a list of fruits and vegetables.
I want to have formula that would go through the list and for every fruit (i.e apples, oranges, bananas etc.) it sees, it counts as 1 fruit.

Example:

Apple
Orange
Orange
Lettuce
Banana
Apple
Apple
Broccoli

Fruit = 6

I think it will involve using arrays, but I don't have any true experience using those. So please be detailed in your explanations.

Thanks,

**acsishere** · 04-26-2008, 02:31 PM

Do you look for VLOOKUP Function.

If so, I attached a file. In that example, you can increase the number of fruits/vegs as you like (only you need to name those cells). Please let me know if you find any difficulty.

A small modification I made. Please see now.

acsishere.

**daddylonglegs** · 04-26-2008, 02:44 PM

If fruit & veg is in A1:A10 this formula will give a unique count

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

**aviavy** · 04-26-2008, 03:01 PM

So basically you're applying a single code to all fruits and then counting the amount times the code appears.
Can you explain on your VLOOKUP formula, I've only started using those 2 days ago.