Return Min and Max using multiple IF conditions

[Jazz Jones]

In Column A, I have team function, in Column H I have Status, in Column S, I have Weeks Active.

I need to return the minimum and maximum values in S (not including 0) if A = "Chief Operating Office" AND H = "Active"

Does anyone know how to get this going? I'm dying inside trying to figure it out.

Thanks
Jasmine

 

[wideboydixon]

in Excel 2016 you have MAXIFS. However, for earlier versions you can use an array formula I think:

{=MAX(IF(A:A="Chief Operating Office",IF(H:H="Active",S:S)))}

Enter with Ctrl+Shift+Enter (don't try and enter the {} yourself. Similar solution for MIN.

WBD

 

[Jazz Jones]

This has sorted it and allowed me to create some awesome-looking graphs and charts. Thank you - really appreciate the help.

 

[Jazz Jones]

Another question, along a similar vein... I'm currently using the below formula to calculate min/max information for the active dataset. However, I now need to pull it from past data as well. There are two types of past data, one is "Future Joiner" and one is "Starter Commenced" - I know that these will replace the 'Active' condition under Type but I don't know how to get the information from BOTH options. Is this easily doable?


=MIN(IF(Table2[Function]="Retail & Digital",IF(Table2[Type]="Active",Table2[Calculated Full Weeks Active])))

 

[wideboydixon]

Untested but it might work:

=MIN(IF(Table2[Function]="Retail & Digital",IF(Table2[Type]="Future Joiner",Table2[Calculated Full Weeks Active],IF(Table2[Type]="Starter Commenced",Table2[Calculated Full Weeks Active]))))

WBD