| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summary of all values and occurrence of each value
I got a problem where I am looking for an elegant solution. I got an table containing data, telling me a certain number. What I want is a summary of all the numbers in that table and telling me how many times each number occours. There can be gaps between these numbers, lets say the number 90 occurs 12 times, number 95 occurs 2 times, number 98 occurs 5 times and so on and on. Help would be very much appreciated, thanks -- martho ------------------------------------------------------------------------ martho's Profile: http://www.excelforum.com/member.php...o&userid=29144 View this thread: http://www.excelforum.com/showthread...hreadid=488627 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summary of all values and occurrence of each value
Assuming the numbers are in A1:A100
B1: =A1 B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2 0&""),0))) which is an array formula, so commit with Ctrl-Shift-Enter, and copy doun C1: + COUNTIF(A:A,B1) and copy down -- HTH RP (remove nothere from the email address if mailing direct) "martho" wrote in message ... I got a problem where I am looking for an elegant solution. I got an table containing data, telling me a certain number. What I want is a summary of all the numbers in that table and telling me how many times each number occours. There can be gaps between these numbers, lets say the number 90 occurs 12 times, number 95 occurs 2 times, number 98 occurs 5 times and so on and on. Help would be very much appreciated, thanks -- martho ------------------------------------------------------------------------ martho's Profile: http://www.excelforum.com/member.php...o&userid=29144 View this thread: http://www.excelforum.com/showthread...hreadid=488627 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match Each Numeric occurrence and Return Individual Rows of Data | Excel Worksheet Functions | |||
| Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
| occurrence (last) reference | Excel Discussion (Misc queries) | |||
| Matching a List Containing Redundant Values | Excel Discussion (Misc queries) | |||
Linear Mode
