Excel Formula Help: MAXIFS over two sheets

[Rob_010101]

Hello,

A formula I am trying to use:

=IF(A2<>0, MAXIFS('Current 6 months'!J:J, 'Current 6 months'!A:A, A2)+MAXIFS('Previous 6 months'!J:J, 'Previous 6 months'!A:A, A2), "")

Is returning weird results.

It has to look over two sheets and return the latest absence start date from column J

- =IF(A2 is the employee ID
- 'current 6 months'!J:J and 'previous 6 months'!J:J' are the columns where the dates are
- 'current 6 months'!A:A', A2 and 'previous 6 months'!A:A', A2 is the employee ID, matching A2 in the main sheet.

So, it's basically looking at in column A in the main sheet where the formula is, then finding the same employee ID in column A in both sheets, looking down column J in each and returning the latest date.

Hope that makes sense.

Kind Regards
Chris

 

[Fluff]

How about

=IF(A2<>0, MAX(MAXIFS('Current 6 months'!J:J, 'Current 6 months'!A:A, A2),MAXIFS('Previous 6 months'!J:J, 'Previous 6 months'!A:A, A2)), "")

 

[Rob_010101]

How about

=IF(A2<>0, MAX(MAXIFS('Current 6 months'!J:J, 'Current 6 months'!A:A, A2),MAXIFS('Previous 6 months'!J:J, 'Previous 6 months'!A:A, A2)), "")

It was as simple as that.

Thanks very much!

 

[Fluff]

You're welcome & thanks for the feedback.