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

[Ron Coderre]

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
>
>

[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
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

[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

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