Maximum Value For Each Item in List

[gtbear]

Hello,

I've been searching for the best way to achieve this result.

I have a spreadsheet with arounf 1500 rows.

Column A Contains Location Names, Column B contains Department Names and Column contains employee count.

I am trying to find a formula that will give me the department with the highest number of employees for each location.

Any Help is greatly appreciated.

Regards

David

[Fotis1991]

Hi

Maybe using an Array MAX(IF formula.

Something like this

=MAX(IF(A1:A100=A1,BI:B100=B1,C1:C100)))

But it's better yo upload a small sample workbook.

[gtbear]

Example.xlsx
Hi Fotis, thanks for replying so quickly

Here's a small example spreadsheet

[Fotis1991]

Maybe in D2 and copy down?

=SUMPRODUCT(($A$3:$A$35=A3)*($B$3:$B$35=B3)*($C$3:$C$35))

[gtbear]

I think I may have mislead with the question.

I need to find which Pay Group is the most common for each Location.

e.g. for Academic Centre A it would be WUPS for Academic Office it would be RTPS

Regards

David

[Fotis1991]

Hi

Let's try this.

In g3 and copy down, put this Array formula(Confirm using Control+Shift+Enter-Not just Enter)

Please Login or Register  to view this content.

In H3 and copy down this Array formula.

Please Login or Register  to view this content.

Is this works for you?

[gtbear]

Thanks Fotis, works perfectly