max if formula

[Corleone]

trying to get a formula to provide the latest date in column f based on the criteria in column b
however, im just getting "#value"

anyone know what im missing below

=MAX((DETAIL_ACTIVITIES!B1:B3000="IG1--Cantilever Base OLE")*(DETAIL_ACTIVITIES!F2:F3000))

thanks

 

[T. Valko]

trying to get a formula to provide the latest date in column f based on the criteria in column b
however, im just getting "#value"

anyone know what im missing below

=MAX((DETAIL_ACTIVITIES!B1:B3000="IG1--Cantilever Base OLE")*(DETAIL_ACTIVITIES!F2:F3000))

thanks

Your ranges aren't the same size:

DETAIL_ACTIVITIES!B1:B3000
DETAIL_ACTIVITIES!F2:F3000

Array entered**:

=MAX(IF(DETAIL_ACTIVITIES!B2:B3000="IG1--Cantilever Base OLE",DETAIL_ACTIVITIES!F2:F3000))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[Corleone]

Your ranges aren't the same size:

DETAIL_ACTIVITIES!B1:B3000
DETAIL_ACTIVITIES!F2:F3000

Array entered**:

=MAX(IF(DETAIL_ACTIVITIES!B2:B3000="IG1--Cantilever Base OLE",DETAIL_ACTIVITIES!F2:F3000))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Thanks for this IT WORKS PERFECTLY

 

[T. Valko]

Thanks for this IT WORKS PERFECTLY

You're welcome. Thanks for the feedback!

 

[Corleone]

just tried adding in an extra condition and changing the if statement to an IF AND so that it picks the latest date based on both criteria in column B and C being met but it just returns 01/01/1900 am i missing something here

thanks

=MAX(IF(AND(DETAIL_ACTIVITIES!B2:B3000="IG01--Cantilever Base OLE",DETAIL_ACTIVITIES!C2:C3000="TIF 2 Bescosth"),DETAIL_ACTIVITIES!F2:F3000))

 

[jasonb75]

Try

=MAX(IF((DETAIL_ACTIVITIES!B2:B3000="IG01--Cantilever Base OLE")*(DETAIL_ACTIVITIES!C2:C3000="TIF 2 Bescosth"),DETAIL_ACTIVITIES!F2:F3000))

instead, remember to confirm with Shirt Ctrl Enter.

 

[Corleone]

Many thanks both for your help with solving this for me

 

[T. Valko]

just tried adding in an extra condition and changing the if statement to an IF AND so that it picks the latest date based on both criteria in column B and C being met but it just returns 01/01/1900 am i missing something here

thanks

=MAX(IF(AND(DETAIL_ACTIVITIES!B2:B3000="IG01--Cantilever Base OLE",DETAIL_ACTIVITIES!C2:C3000="TIF 2 Bescosth"),DETAIL_ACTIVITIES!F2:F3000))

How about using cells to hold the criteria:

• A1 = IG01--Cantilever Base OLE
• A2 = TIF 2 Bescosth

Still array entered**:

=MAX(IF(DETAIL_ACTIVITIES!B2:B3000=A1,IF(DETAIL_ACTIVITIES!C2:C3000=A2,DETAIL_ACTIVITIES!F2:F3000)))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[Corleone]

How about using cells to hold the criteria:

• A1 = IG01--Cantilever Base OLE
• A2 = TIF 2 Bescosth

Still array entered**:

=MAX(IF(DETAIL_ACTIVITIES!B2:B3000=A1,IF(DETAIL_ACTIVITIES!C2:C3000=A2,DETAIL_ACTIVITIES!F2:F3000)))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

This is the approach i will adopt as the user has a categoty tab where he/she will input the
work types ie "IG01--Cantilever Base OLE" and Area "TIF 2 Bescosth" on that page so i can then do just as you suggest above
Cheers

 

[T. Valko]

This is the approach i will adopt as the user has a categoty tab where he/she will input the
work types ie "IG01--Cantilever Base OLE" and Area "TIF 2 Bescosth" on that page so i can then do just as you suggest above
Cheers

Thanks for the feedback!