table statistics

j3andc

Board Regular
Joined
Mar 4, 2002
Messages
170
Jim Test1.xls
ABCD
1
2ABC
31JimJoe
42JoeJim
53JeffMarkJim
64JoeMark
75Jeff
86MarkJeff
9
10
11Jim123
12Joe124
13Mark346
14Jeff356
Sheet1


From the above example, I am trying to use a formula to provide the statistics in rows 11 - 14 (by name, the number of each row that the name can be found). In its current layout, I can not figure out how to use a formula to do this.

Any help would be greatly appreciated.

Jim
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,402
Assuming that a name occurs only once in any row, try the following...

B11, copied across and down:

=IF(COLUMNS($B11:B11)<=COUNTIF($B$3:$D$8,$A11),SMALL(IF($B$3:$D$8=$A11,$A$3:$A$8),COLUMNS($B11:B11)),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Post back if a name can occur more than once in any row.

Hope this helps!
 

j3andc

Board Regular
Joined
Mar 4, 2002
Messages
170
Great! I was able to make it work.

Now to understand it.

What is the initial comparison for? (is the number of columns less than or equal to the number of times the name shows up?)
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,402
j3andc said:
What is the initial comparison for? (is the number of columns less than or equal to the number of times the name shows up?)

Yes, that's exactly the case. When COLUMNS($B11:B11) returns a number that is greater than the number of times the name shows up, the formula returns a blank instead of #NUM!, which would otherwise occur without the IF statement.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,604
Members
412,330
Latest member
carlosjw
Top