MAXIFS Based On One Criteria

[excelbytes]

I have a workbook with 5 worksheets, Sheet1 through Sheet5 plus a Totals worksheet. Each of the 5 worksheets has a list of players in column A and a total of their scores in column E. There are 18 different players, but the order that they are in on the 5 worksheets varies.

I need to enter a formula on the Totals worksheet for each player that will provide the MAX value for each player across all 5 worksheets. I tried:

=MAXIFS(Sheet1:Sheet5!E2:E19,Sheet1:Sheet5!A2:A19,Total!G3)

and get a #VALUE! error.

Is this possible?

 

[6StringJazzer]

MAXIFS does not support 3D formulas. VSTACK does, so you might be able to do this but I'm not going to take the time to set up a file that matches yours to test it.

=MAXIFS(VSTACK(Sheet1:Sheet5!E2:E19),VSTACK(Sheet1:Sheet5!A2:A19),G3)

 

[Peter_SSs]

I don't think that you can do what @6StringJazzer has suggested because MAXIFS requires range inputs, not arrays. However using that concept this way might suit you?

Utilizing 2 spare columns on the 'Totals' sheet (I used columns Y:Z) in cell Y3 of 'Totals' I put the formula

=CHOOSECOLS(VSTACK(Sheet1:Sheet5!A2:E19),1,5)

These two columns could them be hidden if you want.

Then to get the max values, this formula (in H3?)

=MAXIFS(TAKE(Y3#,,-1),TAKE(Y3#,,1),G3:G20)

 

[6StringJazzer]

I don't think that you can do what @6StringJazzer has suggested because MAXIFS requires range inputs, not arrays. However using that concept this way might suit you?

My apologies, I didn't test it.

 

[Peter_SSs]

My apologies, I didn't test it.

I don't think that you need to apologise since you did put a 'non-test' disclaimer in your post.

 

[excelbytes]

Someone came up with this formula:

=MAX(MAXIFS(INDIRECT("'"&Sheets&"'!"&"E1:E18"),INDIRECT("'"&Sheets&"'!"&"A1:A18"),A2))

Where "Sheets" is the named range for all the worksheet names. It works perfectly, but I'm not sure I understand why. Any thoughts?

 

[Peter_SSs]

I would be avoiding the volatile function INDIRECT if possible.
Here is another option (non-volatile), doesn't require the helper columns I suggested earlier, doesn't require the named range and fills all answers with the formula in a single cell without needing to copy down.

=BYROW(A2:A19,LAMBDA(r,LET(w,WRAPROWS(TOCOL(Sheet1:Sheet5!A1:E18),5),MAX(FILTER(TAKE(w,,-1),TAKE(w,,1)=r)))))