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


--
SydnTex
------------------------------------------------------------------------
SydnTex's Profile: http://www.excelforum.com/member.php...o&userid=36562
View this thread: http://www.excelforum.com/showthread...hreadid=563117

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" wrote:


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


--
SydnTex
------------------------------------------------------------------------
SydnTex's Profile: http://www.excelforum.com/member.php...o&userid=36562
View this thread: http://www.excelforum.com/showthread...hreadid=563117

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


--
SydnTex
------------------------------------------------------------------------
SydnTex's Profile: http://www.excelforum.com/member.php...o&userid=36562
View this thread: http://www.excelforum.com/showthread...hreadid=563117

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

Die_Another_Day
SydnTex wrote:
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


--
SydnTex
------------------------------------------------------------------------
SydnTex's Profile: http://www.excelforum.com/member.php...o&userid=36562
View this thread: http://www.excelforum.com/showthread...hreadid=563117

Hi SydnTex,

also look at:

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

HTH
Martin

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=563117

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.


--
SydnTex
------------------------------------------------------------------------
SydnTex's Profile: http://www.excelforum.com/member.php...o&userid=36562
View this thread: http://www.excelforum.com/showthread...hreadid=563117

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

HTH

Die_Another_Day

SydnTex wrote:
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.


--
SydnTex
------------------------------------------------------------------------
SydnTex's Profile: http://www.excelforum.com/member.php...o&userid=36562
View this thread: http://www.excelforum.com/showthread...hreadid=563117