Ask Your Question

Revision history [back]

click to hide/show revision 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 CTRLEnter in order to have this code be interpreted as an array function.

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.

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 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 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.)

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))
=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 IF():use a value lower than any of yours:

=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.)