First time here? Check out the FAQ!

1 | initial version |

The other answer given here (alas, I cannot see the author's name—weird) nudged me into the right direction. But by specifying a thing like `MAX(ROW($Sheet1.$B$2:$B$13)*($Sheet1.$A$2:$A$13=A2))`

it only takes the value furthest down (by computing the maximum if the row number). Since in this case the dates are sorted and later ones are indeed further down, this works as well. This might not be the case in other scenarios (as it was in mine).

To really get the maximum of a list of values for which a selector next to it matches a criterion (which would be a true `MAXIF()`

), one can use a formula like this:

```
=MAX(INDIRECT("$Sheet1." & ADDRESS(ROW($Sheet1.$B$2:$B$13), 2))*($Sheet1.$A$2:$A$13=A3))
```

And, again: Don't forget to press `CTRL``Enter` in order to have this code be interpreted as an array function.

2 | No.2 Revision |

The other answer given here (alas, I cannot see the author's name—weird) nudged me into the right direction. But by specifying a thing like `MAX(ROW($Sheet1.$B$2:$B$13)*($Sheet1.$A$2:$A$13=A2))`

it only takes the value furthest down (by computing the maximum if the row number). Since in this case the dates are sorted and later ones are indeed further down, this works as well. This might not be the case in other scenarios (as it was in mine).

To really get the maximum of a list of values for which a selector next to it matches a criterion (which would be a true `MAXIF()`

), one can use a formula like this:

```
=MAX(INDIRECT("$Sheet1." & ADDRESS(ROW($Sheet1.$B$2:$B$13), 2))*($Sheet1.$A$2:$A$13=A3))
```

And, again: Don't forget to press `CTRL`-`Enter` in order to have this code be interpreted as an array function.

3 | No.3 Revision |

The other answer given here (alas, I cannot see the author's name—weird) nudged me into the right direction. But by specifying a thing like `MAX(ROW($Sheet1.$B$2:$B$13)*($Sheet1.$A$2:$A$13=A2))`

it only takes the value furthest down (by computing the maximum if the row number). Since in this case the dates are sorted and later ones are indeed further down, this works as well. This might not be the case in other scenarios (as it was in mine).

To really get the maximum of a list of values for which a selector next to it matches a criterion (which would be a true `MAXIF()`

), one can use a formula like this:

```
=MAX(INDIRECT("$Sheet1." & ADDRESS(ROW($Sheet1.$B$2:$B$13), 2))*($Sheet1.$A$2:$A$13=A3))
```

And, again: Don't forget to press CTRL-SHIFT-Enter in order to have this code be interpreted as an array function.`CTRL`-`Enter`

Mind also, that this multiplication with either 1 or 0 will make all values for which the criterion doesn't match just 0 (which in many cases will not end up to be the maximum). If you, however, try to find the maximum of a bunch of negative numbers, this will fail because then the maximum will be any of the zeros. You need to make it more complex then using `IF()`

:

```
=MAX(IF(($Sheet1.$A$2:$A$13=A4), INDIRECT("$Sheet1." & ADDRESS(ROW($Sheet1.$B$2:$B$13), 2)), -693595))
```

(The -693595 being the number for the date 0001-01-01 which is probably before any date you are dealing with.)

4 | No.4 Revision |

`MAX(ROW($Sheet1.$B$2:$B$13)*($Sheet1.$A$2:$A$13=A2))`

it only takes the value furthest down (by computing the maximum if the row number). Since in this case the dates are sorted and later ones are indeed further down, this works as well. This might not be the case in other scenarios (as it was in mine).

`MAXIF()`

), one can use a formula like this:

~~=MAX(INDIRECT("$Sheet1." & ADDRESS(ROW($Sheet1.$B$2:$B$13), 2))*($Sheet1.$A$2:$A$13=A3))
~~=MAX(IF($Sheet1.$A$2:$A$13=A2, $Sheet1.$B$2:$B$13, 0))

**And, again: Don't forget to press CTRL-SHIFT-Enter in order to have this code be interpreted as an array function.**

Mind also, that this ~~multiplication with either 1 or 0 ~~will make all values for which the criterion doesn't match just 0 (which in many cases will not end up to be the maximum). If you, however, try to find the maximum of a bunch of negative numbers, this will fail because then the maximum will be any of the zeros. You need to ~~make it more complex then using ~~use a value lower than any of yours:`IF()`

:

~~=MAX(IF(($Sheet1.$A$2:$A$13=A4), INDIRECT("$Sheet1." & ADDRESS(ROW($Sheet1.$B$2:$B$13), 2)), ~~=MAX(IF($Sheet1.$A$2:$A$13=A4, $Sheet1.$B$2:$B$13, -693595))

(The -693595 being the number for the date 0001-01-01 which is probably before any date you are dealing with.)

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.