I have got a challenge from a colleague:

He has a spreadsheet he uses for collection component errors. Each time he
has an error, the number of the component i registered. The numbers are in
the A-column. Now he wants to know how many components has beeen registerede
once (on error), twice (two errors) and so on up to the largest number of
registered errors. He does'nt care about which component it is. He only
wants the number.

Imange this list
A1
A2
A1
A4
A1
A6
A3
A4
A2
A8

The resoldt be somethin like:

One registration 3 components (A8, A3 and A6)
Two registrations 2 components (A2 and A4)
Three Registrations 1 component (A1)

and so on.


PIVOT is not an option. Neither is anything that demands a manuel
manipulation of the spreadsheet before the caluclation is done, so it has to
be done by a formula. I have considered SUBTOTAL og FREQUENCY, but neither
of them does the trick.

Any suggestions

Jan

If we are allowed a helper column:
In D1 enter =COUNTIF($A$1:$A$10,A1), copy down to bottom of list
In column E the series 1,2,3,4,5,6...10 (or whatever max is expected)
In F1 use =COUNTIF($D$1:$D$10,E1)/E1 and copy down as far as E goes
Column E and F are you answers
Interested in a macro answer?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jan Kronsell" wrote in message
...

I have got a challenge from a colleague:

He has a spreadsheet he uses for collection component errors. Each time
he has an error, the number of the component i registered. The numbers are
in the A-column. Now he wants to know how many components has beeen
registerede once (on error), twice (two errors) and so on up to the
largest number of registered errors. He does'nt care about which component
it is. He only wants the number.

Imange this list
A1
A2
A1
A4
A1
A6
A3
A4
A2
A8

The resoldt be somethin like:

One registration 3 components (A8, A3 and A6)
Two registrations 2 components (A2 and A4)
Three Registrations 1 component (A1)

and so on.


PIVOT is not an option. Neither is anything that demands a manuel
manipulation of the spreadsheet before the caluclation is done, so it has
to be done by a formula. I have considered SUBTOTAL og FREQUENCY, but
neither of them does the trick.

Any suggestions

Jan

Thank you. I got to that solution myself. The problem is, that i dont know
the mazximun number of errors. It couuld be anything between 1 and several
hundred.
Unfortunately trhe spreadsheet shall be used by people with no
excel-knowledge at all. And macros are not an option.¨

But if i dont get any other replies, I will go with your solution.

Jan

Bernard Liengme wrote:
If we are allowed a helper column:
In D1 enter =COUNTIF($A$1:$A$10,A1), copy down to bottom of list
In column E the series 1,2,3,4,5,6...10 (or whatever max is expected)
In F1 use =COUNTIF($D$1:$D$10,E1)/E1 and copy down as far as E goes
Column E and F are you answers
Interested in a macro answer?
best wishes

"Jan Kronsell" wrote in message
...

I have got a challenge from a colleague:

He has a spreadsheet he uses for collection component errors. Each time
he has an error, the number of the component i registered. The
numbers are in the A-column. Now he wants to know how many
components has beeen registerede once (on error), twice (two errors)
and so on up to the largest number of registered errors. He does'nt
care about which component it is. He only wants the number.

Imange this list
A1
A2
A1
A4
A1
A6
A3
A4
A2
A8

The resoldt be somethin like:

One registration 3 components (A8, A3 and A6)
Two registrations 2 components (A2 and A4)
Three Registrations 1 component (A1)

and so on.


PIVOT is not an option. Neither is anything that demands a manuel
manipulation of the spreadsheet before the caluclation is done, so
it has to be done by a formula. I have considered SUBTOTAL og
FREQUENCY, but neither of them does the trick.

Any suggestions

Jan

Hi,

Suppose your list is in column A and you enter the numbers you want to count
in C1:C3..., in this case that would be 1, 2, 3,...
then in D1 enter the formula:

=SUMPRODUCT(--(COUNTIF($A$1:$A$11,$A$1:$A$11)=C1))/C1

And copy it down as far as necessary

If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Jan Kronsell" wrote:


I have got a challenge from a colleague:

He has a spreadsheet he uses for collection component errors. Each time he
has an error, the number of the component i registered. The numbers are in
the A-column. Now he wants to know how many components has beeen registerede
once (on error), twice (two errors) and so on up to the largest number of
registered errors. He does'nt care about which component it is. He only
wants the number.

Imange this list
A1
A2
A1
A4
A1
A6
A3
A4
A2
A8

The resoldt be somethin like:

One registration 3 components (A8, A3 and A6)
Two registrations 2 components (A2 and A4)
Three Registrations 1 component (A1)

and so on.


PIVOT is not an option. Neither is anything that demands a manuel
manipulation of the spreadsheet before the caluclation is done, so it has to
be done by a formula. I have considered SUBTOTAL og FREQUENCY, but neither
of them does the trick.

Any suggestions

Jan

The problem is, that I dont know the numbers I want to count, because I dont
know what they are.

Jan

Shane Devenshire wrote:
Hi,

Suppose your list is in column A and you enter the numbers you want
to count in C1:C3..., in this case that would be 1, 2, 3,...
then in D1 enter the formula:

=SUMPRODUCT(--(COUNTIF($A$1:$A$11,$A$1:$A$11)=C1))/C1

And copy it down as far as necessary

If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Jan Kronsell" wrote:


I have got a challenge from a colleague:

He has a spreadsheet he uses for collection component errors. Each
time he has an error, the number of the component i registered. The
numbers are in the A-column. Now he wants to know how many
components has beeen registerede once (on error), twice (two errors)
and so on up to the largest number of registered errors. He does'nt
care about which component it is. He only wants the number.

Imange this list
A1
A2
A1
A4
A1
A6
A3
A4
A2
A8

The resoldt be somethin like:

One registration 3 components (A8, A3 and A6)
Two registrations 2 components (A2 and A4)
Three Registrations 1 component (A1)

and so on.


PIVOT is not an option. Neither is anything that demands a manuel
manipulation of the spreadsheet before the caluclation is done, so
it has to be done by a formula. I have considered SUBTOTAL og
FREQUENCY, but neither of them does the trick.

Any suggestions

Jan