# table statistics

#### j3andc

##### Board Regular
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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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!

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?)

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.

Replies
2
Views
138
Replies
4
Views
177
Replies
11
Views
370
Replies
4
Views
220
Replies
12
Views
461

1,203,605
Messages
6,056,251
Members
444,853
Latest member
sam69

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back