Return Top Criteria Based on Sum of Hours

[Manwithaplan]

Hi

I want to return the job with the highest (and second highest) sum of hours by machine from the INPUT tab and also return the sum of those hours in the cell below. For example for the Cat - Grader - 140 machine, the top job so far this year was R&M - Roads with 36 hours, while the second highest for this machine was CAPEX with 22 hours

Info below

Hours sum range = INPUT!$J$3:$J$300
Job range = INPUT!$E$3:$E$300
Machine criteria range = INPUT!$D$3:$D$300
Machine = SUMMARY!D$2


The results should return to SUMMARY!D4, SUMMARY!D5 (for the top ranked job and sum of hours), SUMMARY!D6, SUMMARY!D7 (for the second highest ranked job and sum of hours)

Thanks in advance

Mike

[avk]

Try in "D5"

Please Login or Register  to view this content.

In "D7"

Please Login or Register  to view this content.

[Manwithaplan]

Hi, appreciate your help but its the formula in D4 and D6 that i am after

[shukla.ankur281190]

Give this try in

D4

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy across.

[avk]

please enter require result in respective cell.

[shukla.ankur281190]

AVK OP asked D4 to F7 result by formula

[FlameRetired]

Hi, appreciate your help but its the formula in D4 and D6 that i am after

In D4 array enter this formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

In D6 array enter this.

Please Login or Register  to view this content.

[FlameRetired]

If still need hours array enter this in D5

Please Login or Register  to view this content.

and in D7

Please Login or Register  to view this content.

[shukla.ankur281190]

@Dave if you do apply your formula the cell reference counter each other and circular reference error would show !!

[Manwithaplan]

Thanks FlameRetired, this works well. I do however try to avoid array formulas where possible. Is there any other way to do this that doesnt require an array formula?

[FlameRetired]

@Dave if you do apply your formula the cell reference counter each other and circular reference error would show !!

Yes. I noticed that later. I jumped into the middle of the thread assuming the hours were already solved. My bad.

[FlameRetired]

Thanks FlameRetired, this works well. I do however try to avoid array formulas where possible. Is there any other way to do this that doesnt require an array formula?

I'll look into that. Right now it is way past my bed time.

[Manwithaplan]

No problem, thanks for your help. Look forward to see what you come up with

[Manwithaplan]

See D4 to D7

[FlameRetired]

When you say you want to avoid array formulas is the issue Ctrl + Shift + Enter, or is it something else? There are array formulas that don't require that. In either event this may require some helper columns. Are you open to this?

[Manwithaplan]

When you say you want to avoid array formulas is the issue Ctrl + Shift + Enter, or is it something else? There are array formulas that don't require that. In either event this may require some helper columns. Are you open to this?

A bit of the ctr shift enter (for other users) and they also tend to be heavier in calculation. I am open to helper columns

[FlameRetired]

Good deal.

None of these are array formula.

In 'INPUT' sheet cell N1 is today's date.

In N3 and filled down returns the row number of the first occurrences (and therefore unique) instances of Work Types. These will be the index numbers for the Work Type headers helper columns.

Please Login or Register  to view this content.

The Work Type headers output with this formula in P2 and filled across.

Please Login or Register  to view this content.

The first occurrence (unique) of each Machine returns with this in O3 and filled down.

Please Login or Register  to view this content.

Then the hour summaries return with this in P3 filled down and across.

Please Login or Register  to view this content.

On 'SUMMARY' sheet this for the 1st place Work Type by Machine. In D4 and filled across.

Please Login or Register  to view this content.

Copy that range D4:F4 and paste to D6 to return 2nd place.

For the most hours this in D5 and across.

Please Login or Register  to view this content.

Copy D5:F5 and paste to D7 to return 2nd most hours.

[Manwithaplan]

Cheers Ill give it a go thanks heaps for your help.

You might also know the answer to this post which i thought would be an easy fix but has been dragging on a bit
https://www.excelforum.com/excel-for...nk-values.html

[FlameRetired]

You are welcome. I'll have a look at the other thread as time permits.