MAXIF Excel

Question

Good afternoon,

In a excel worksheet we have the column 'salers'

A1 = Bill

A2 = John

A3 = Jack

etc.

And the column 'sales'

B1 = 200

B2 = 210

B3 = 195

etc.

We need in cell C1 the formula of the largest (MAX) 'saler' ,to auto put the name of the 'saler' (in this case is 'John' A2).

And in cell D1 the formula of the largest (MAX) 'sales' , to auto put the number of the 'sales' (in this case is '210' B2).

We also need in C2-D2 the 2nd , in C3-D3 the 3rd ,etc.

It needs to change automatically because the numbers of the sales changes every day.

Thanks in advance

Answer 1

Things would be much simpler if you just sorted columns A and B on column B descending.

If that is not possible, then enter the formula

=LARGE(B$1:B$3,ROW()-ROW(A$1)+1)

in cell D1 and enter the formula

=XLOOKUP(D1,B$1:B$3,A$1:A$3,"?",0,1)

in cell C1. Change each $3 (three of them) to match the last row of data in your worksheet. Copy these two cells to all the cells down to the last row of data.

You will have to deal with the case when two salers have the same sales.

Answer 2

Hi,

In cell D2, enter this formula

=SORT(A2:B4,2,-1)

Hope this helps.