Summing Unique data values

[SydnTex]

I am trying, with no luck to find a formula will sum the amount of times
unique data is in a list, eg range b2:b6 contains

apple
pear
pear
house
apple

Given data is unique cannot check it against a table but would like to
count number of times each instance appears

apple 2
pear 2
house 1

Can this be done without looking up a dataset.

Thanks

SydnTex

 

[Guest]

Have you considered using a Pivot Table?

First, make sure your list has a column heading. I'll assume FRUIT.

Then...
<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the FRUIT field here
DATA: Drag the FRUIT field here
If it doesn't list as Count of FRUIT...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each Fruit and the count of each instance.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[SydnTex]

Thank you, it works a treat.

One more question then on Pivot tables, can i have them automatically
update as the spreadsheet is updated.

I am entering data from a form, could i put a line in the cmdOnClick
function that will force the pivot table to update.


Cheers,

SydnTex

 

[Die_Another_Day]

="Apple = " &COUNTIF(B2:B6,"apple")
Try using CountIF like that.

Die_Another_Day

 

[MartinW]

Hi SydnTex,

also look at:

=COUNTIF(B2:B6,"apple")
=COUNTIF(B2:B6,"pear")
=COUNTIF(B2:B6,"house")

HTH
Martin

 

[Ron Coderre]

SydnTex

See the RefreshTable method in VBA help.

BTW...If you don't really need the pivot table refreshed until input is
completed, you many want to associate the refresh with Unload or
Deactivate.

Regards,

Ron

 

[SydnTex]

I am having trouble displaying a list in a combo or list box from data
in the spreadsheet.

data is held in cells A5:A9 and i have the following procedure to fill
the box but it does not add it overwrites the data giving only one
reference, the last.

Do
If IsEmpty(ActiveCell) = False Then
cboSpeakerTopic.Value = ActiveCell.Value
ActiveCell.Offset(1, 0).Select

End If
Loop Until IsEmpty(ActiveCell) = True

How do i get it to add into list rather than overwrite.

Thanks.

 

[Die_Another_Day]

You almost answered your own question.
cboSpeakerTopic.Value = ActiveCell.Value
Should look like this:
cboSpeakerTopic.AddItem pvargitem:=ActiveCell.Value

HTH

Die_Another_Day