Counting number of unknown numbers

[Jan Kronsell]

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

 

[Bernard Liengme]

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]

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

 

[Shane Devenshire]

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]

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

Jan