Hi Christian,

Firstly, the formulas are only going to find one MAX. If you have more than
one with the MAX value then I dont know how to handle that. However, you
will be able to view them on Sheet2 on the lookup table.

I really don't know how much help you need so I have gone in for the
overkill and hopefully you can sort it out.

Assume Sheet1 for your day of week and time of day data as per example you
posted.

The following is on Sheet2:-

Unique list of project names in Column B of Sheet2. Using your example
Project Names is the column header and the projects are in cells B2:B5.
(Column A blank at this stage)

Column B
Project Names
Alpha
Beta
Omega
Delta

In Cell A2 formula:-
=COUNTIF(Sheet1!$B$2:$F$4,B2)
Where Sheet1!$B$2:$F$4 is the range in Sheet1 containing all of your
projects under day of week and time of day. B2 is the first project name
(Alpha) in the unique list of project names in column B of on Sheet2.

Drag this formula to the bottom of the project names and in column A you
will have a list of numbers representing the count of each project name.
Should now look like this with numbers in column A and projects in column B:-

3 Alpha
3 Beta
1 Omega
1 Delta

In a blank cell under the count of names insert this formula:-

=MAX($A$2:$A$5)
Where $A$2:$A$5 is the list of numbers representing the count of each
project name. It returns the highest number. Should now look like this:-

3 Alpha
3 Beta
1 Omega
1 Delta
3

The following is on Sheet1:-

In any blank cell enter the following formula:-

=VLOOKUP(Sheet2!$A$7,Sheet2!$A$2:$B$5,2,FALSE)

Where Sheet2!$A$7 is the cell reference with the MAX formula.
Sheet2!$A$2:$B$5 is the range (2 columns) with the counts of projects plus
the project names.
2 represents the value in the second column to be displayed.
False means exact match only (not nearest match)

Basically the formula looks up the project name with the highest count.

In any other blank cell on Sheet1 for total time on max count project:-

Custom Format the cell to [h]:mm (The square brackets around the h
prevents Excel from displaying the time of the next day if the number of
hours exceed 24. Example 30:00 would show as 6:00 because Excel thinks it is
6:00 am the next day.)

Insert the following formula for the total time spent on the project with
the max count:-

=Sheet2!$A$7*TIMEVALUE("0:15")

Where Sheet2!$A$7 contains the MAX formula. TIMEVALUE("0:15") simply
converts the time entered as text into a numeric value. (You can enter the
time in another cell and simply insert the cell reference in place of "0:15".)

The VLOOKUP formula and the one to calculate the total time can actually
have the MAX formula nested in lieu of placing the max formula in a separate
cell and referencing the cell. Examples as follows:-

=VLOOKUP(MAX(Sheet2!$A$2:$A$5),Sheet2!$A$2:$B$5,2, FALSE)

Calculate the total time on max count project:-

=MAX(Sheet2!$A$2:$A$5)*TIMEVALUE("0:15")

Hope you have understood all this so far because now I am going to explain
how to make it even better.

You indicated that the number of project references could grow. Therefore, I
suggest that you name the ranges in your lookup sheet (Sheet2) and use the
named ranges in your formulas so that when the ranges alter in size all you
have to do is rename the ranges and the formulas will still be correct.

There are 2 ranges to name.

First range:-
Occurrences This will apply to Sheet2!$A$2:$A$5 (only one column)

Project_Reference This will apply to Sheet2!$A$2:$B$5 (2 columns)

The formulas will now look like this where the MAX is nested into the
formula:-

=VLOOKUP(MAX(Occurrences),Project_Reference,2,FALS E)

=MAX(Occurrences)*TIMEVALUE("0:15")

Feel free to get back to me if you have any further questions.

Regards,

OssieMac