TOP
# Maximal value by condition (MAXIF)

In this example we review a formula which finds maximal value by condition. In the standard set in **Excel** is only simple functions for searching for maximal (**MAX**) and minimal (**MIN**) values.

To write our own formula we need use two standard functions: **MAX** and **IF**. We will include **IF()** function into **MAX()** one and input them as a massive formula. For this input in cell **D15**:

**=MAX(IF($B$2:$B$11=B15;$D$2:$D$11))**

and press simultaneously **Ctrl+Shift+Enter** (accordingly input a massive formula). Then it remains stretch the array formula down.

We will have such result:

Also we can see that the formula enclosed in figure brackets (**{ }**), it is indication that we input an array formula.

Similarly we can construct the formula for **MIN()** function:

**=MIN(IF($B$2:$B$11=B15;$D$2:$D$11))**

- 1. What is VBA, basic concepts.
- 2. Debugger (Debugger)
- 3. Working with sheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties (Properties)
- 6.1. Data types (Variables)
- 6.2. Data Types (Continued)
- 7.1. Conditions (Conditions)
- 7.2. Terms (Continued)
- 8.1. Loops (Loops)
- 8.2. Cycles (Continued)
- 9. Procedures and functions
- 10. Dialog windows
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls (Controls)
- 12.3. Control elements (Continued)
- 12.4. Control elements (Exercises)
- 13.1. Arrays (Arrays)
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. SQL language, basic concepts.
- 2. Data selection (SELECT)
- 3. Data sorting (ORDER BY)
- 4. Data filtering (WHERE)
- 5. Use of metacharacters (LIKE)
- 6. Calculation (calculation) fields
- 7. Data processing functions
- 8. Data grouping (GROUP BY)
- 9. Sub-requests
- 10. Combination of tables (INNER JOIN)
- 11. OUTER JOIN
- 12. Combined requests (UNION)
- 13. Adding data (INSERT INTO)
- 14. Creating tables (CREATE TABLE)

- Import of currency rates from the NBU website
- Sum written in English
- Unpivottables (Unpivot)
- Function
Google Translate - Camel Case Function
- Snake Case Function
- Kebab Case Function
- Break Case Function
- Sentence Case Function
- Title Case Function
Fuzzy Lookup Function- Function GETSUBSTR
- Free extension "YouLibreCalc"
- Professional Extension "YLC Utilities"