counting numbers only once
- barry
- FISO Knight
- Posts: 18871
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
counting numbers only once
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
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
- 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
- FPL:
Re: counting numbers only once
SUM and FREQUENCY should do it
- 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
- FPL:
Re: counting numbers only once
=SUM(IF(FREQUENCY(A1:A46000,A1:A46000)>0,1))
- barry
- FISO Knight
- Posts: 18871
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Re: counting numbers only once
cheers chief, I'll go test that.
- barry
- FISO Knight
- Posts: 18871
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Re: counting numbers only once
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.
thanks again.
- 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
- FPL:
Re: counting numbers only once
Strange it needs a formula as it is a fairly common request.
Should be a built in function
Should be a built in function
- barry
- FISO Knight
- Posts: 18871
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Re: counting numbers only once
that's what I thought!Surprised wrote:Strange it needs a formula as it is a fairly common request.
Should be a built in function
- murf
- FISO Viscount
- Posts: 109450
- Joined: 13 Oct 2005, 18:28
- Location: here
- FS Record: Once led TFF. Very briefly.
- FPL:
- Contact:
Re: counting numbers only once
Could be a nightmare though if any numbers aren't exact whole numbers due to rounding up etc etcbarry wrote:that's what I thought!Surprised wrote:Strange it needs a formula as it is a fairly common request.
Should be a built in function
- 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
- FPL:
Re: counting numbers only once
True.
Self made formulas are far better as you can make them do whatever you want.
Self made formulas are far better as you can make them do whatever you want.
- eagle224
- Grumpy Old Man
- Posts: 2427
- Joined: 10 Nov 2005, 22:14
- Location: England
- FS Record: Veteran
- FPL:
Re: counting numbers only once
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
Instructions from Microsoft Office site
- Tricky Tree
- FISO Knight
- Posts: 15657
- Joined: 13 Oct 2005, 18:33
- Contact:
Re: counting numbers only once
Go to data and choose remove duplicates?
View Latest: 1 Day View Your posts