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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,224
Office Version
  1. 365
Platform
  1. Windows
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
20,224
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,053
Messages
5,835,149
Members
430,342
Latest member
Sailingexcel

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top