Counting names, total time, and comparing sums

I've been wanting to put together a table of statistics that is frankly a little over-ambitious for my skill level, so I was hoping someone else may be able to share the knowledge required to perform the actions I need.

1) I Have a list of names (for example, column B rows 1 to # are text strings, some empty.) What I would like to do is return the name that appears most often in this array of strings--like what the MODE function does for numerical values. If I need to create a separate table that counts the appearances of each name then grab the corresponding name of the MAX value of that table, that will be fine.

2) I have a table listing in and out times and dates. The formatting is as follows:
Date in (mm/dd/yyyy) | Time in (h:mm <12h>) | Date out (mm/dd/yyyy) | Time out (h:mm <12h>)
What I would like to do here is find the total time in. Now, this will not be as easy as adding the 'tOut-tIn' for each row, because a lot of the time rows will overlap. By this I mean one row's "Time/Date in" could be earlier than the "Time/Date out" in the row before it. Is there a simple function to perform this?
Basically the end result I'm looking for is the number of hours:minutes where there is nothing 'in', but I can calculate this if only I had the number of hours with one or more objects 'in'. I hope this makes sense.

Finally, 3) Throwing back to #1, next to each name there is a number of 'items' recorded. What I would like to do with this is count, again for each name, the total number of items. Say in Row 3 Bill has 6 oranges, Row 4 Jeff has 12, and Row 5 Bill has 2. The output I would like to get from this is Bill having 8 oranges total and Jeff having 12.
The end result from this is that I would like to know who has the most 'items', and how many items that is. If it's possible to do this without creating a new table of names and totals, then all the better.

Any help would be greatly appreciated. Also, if I am posting this in the wrong area, directions to where I should ask for help would be great.

Cheers.

Alright, I've attached a sample chart. From the test values, the return for each of my three problems should be as follows (in my calculations I assume that opening time is 9am and closing time is 6pm, and it is based on a 2007 calendar, therefore 11/3 and 11/4 are weekends):

1) Steve (With 3 submissions. The second, if recorded, would be Chris with 2.)

2) 10:50 (in [h]:mm format) of idle time out of a total 36 business hours. The only times where there is nothing in queue is before 9:10am on 11/2, between 9:20am and 11:00am on 11/6, and from 9:00am until closing time on 11/7.

3) Table (sorted by most items, if possible):
Steve | 1,361
Angela | 1,276
Chris | 753
Petunia | 326
Martin | 126

I hope that's enough information. As I realize this is a very large request, I greatly appreciate any help--big or small. If some of my problems aren't possible within the confines of Excel, it's just as well; at least I can say I tried.

To answer some of your questions

1) I Have a list of names (for example, column B rows 1 to # are text strings, some empty.) What I would like to do is return the name that appears most often in this array of strings--like what the MODE function does for numerical values. If I need to create a separate table that counts the appearances of each name then grab the corresponding name of the MAX value of that table, that will be fine.

Use the function COUNTIF(range,="whatever_name")

Finally, 3) Throwing back to #1, next to each name there is a number of 'items' recorded. What I would like to do with this is count, again for each name, the total number of items. Say in Row 3 Bill has 6 oranges, Row 4 Jeff has 12, and Row 5 Bill has 2. The output I would like to get from this is Bill having 8 oranges total and Jeff having 12.
The end result from this is that I would like to know who has the most 'items', and how many items that is. If it's possible to do this without creating a new table of names and totals, then all the better.

Use SUMIF(range,="whatever_name",range_to_sum)

Ed

Thank you EdMac, that does help.

Now, this may be stretching it a little, but is it possible to automatically populate the COUNTIF lists with new names as they are added to the source table? Or would I need to instead add each new name as I add them to the first list?

Thanks again--I appreciate it.

Hi Will

The countif criteria can be a cell reference, so you could have formula in your table that works on the basis

Please Login or Register  to view this content.

As you fill in the data source with names, they would get picked up.

Ed

Alright, that should really help with #3.

As for #1, I have had some progress. Here's what I have so far (in relation to my sample table):

INDEX(A2:A9,MATCH(MAX(COUNTIF(A2:A9,A2:A9)),COUNTIF(A2:A9,A2:A9),0))

Unfortunately, this is only partly working. The formula currently returns "#N/A", and when I open 'edit formula (=)' I am given the prompt "INDEX: This function has multiple argument lists. Please select one of them." If I choose either of the two, the equation editor does give me the proper result (Steve, in this case) but for some reason the cell still displays "#N/A".

Do I have to somehow deal with the INDEX function's ambiguity?

Hi Will,

It would really helpif you could attached a ZIPPED copy of your sheet so that we can get into the guts of it and see where you are at so far.

If you are using 2007, save it as 2003 style then ZIP it and use the manage attachments button on the post reply to upload.

Ed

Sorry, I know it would really help to have my raw spreadsheet but unfortunately I am not able to share it. The spreadsheet is for work, and the data contained is to be considered confidential; the sample table I attached is quite different from the actual tables I'm working with and serves merely to better explain what I am looking to do.

If no more can be done without seeing the source, I'll have to just continue chipping away at it on my own. I do really appreciate it, though--you have helped a great deal.