To see less ads Register or Login ----- Daily Fantasy Sports games 18+

counting numbers only once

A forum to discuss or share knowledge of spreadsheets.
Post Reply
User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

counting numbers only once

Post by barry »

alright troops, got a massive spreadsheet (46k rows) - whats the best way to quickly check how many different numbers are in one column? i.e.

1,1,1,1,1,2,2,2,2,2,3,3,3,4,5,6,7,8,8,8,8,8,9,10,10,10

would be 10 different numbers.

I had a look at got the following array

=sum(1/countif( a1:a46000,a1:a46000))

got me an answer, although took a while to work it out!
I tested in on a small spreadsheet and it seems to work

BUT

is there a better way?
it seems such an obvious thing to ask excel and I couldn't see a straightforward way to do it.

cheers

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: counting numbers only once

Post by Surprised »

SUM and FREQUENCY should do it

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: counting numbers only once

Post by Surprised »

=SUM(IF(FREQUENCY(A1:A46000,A1:A46000)>0,1))

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: counting numbers only once

Post by barry »

cheers chief, I'll go test that.

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: counting numbers only once

Post by barry »

works great, another suggestion I got was using Pivot tables, I need to learn more about them first, Formula it is for me!

thanks again.

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: counting numbers only once

Post by Surprised »

Strange it needs a formula as it is a fairly common request.
Should be a built in function

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: counting numbers only once

Post by barry »

Surprised wrote:Strange it needs a formula as it is a fairly common request.
Should be a built in function
that's what I thought!

User avatar
murf
FISO Viscount
Posts: 109450
Joined: 13 Oct 2005, 18:28
Location: here
FS Record: Once led TFF. Very briefly.
Contact:

Re: counting numbers only once

Post by murf »

barry wrote:
Surprised wrote:Strange it needs a formula as it is a fairly common request.
Should be a built in function
that's what I thought!
Could be a nightmare though if any numbers aren't exact whole numbers due to rounding up etc etc

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: counting numbers only once

Post by Surprised »

True.
Self made formulas are far better as you can make them do whatever you want.

User avatar
eagle224
Grumpy Old Man
Posts: 2427
Joined: 10 Nov 2005, 22:14
Location: England
FS Record: Veteran

Re: counting numbers only once

Post by eagle224 »

You can do this with a column filter that copies the unique values to another column and then use the ROWS() function to count the results - I believe this works for all data types.

Instructions from Microsoft Office site

User avatar
Tricky Tree
FISO Knight
Posts: 15657
Joined: 13 Oct 2005, 18:33
Contact:

Re: counting numbers only once

Post by Tricky Tree »

Go to data and choose remove duplicates?

View Latest: 1 Day View Your posts
Post Reply

Return to “Spreadsheets”