Minif and Maxif function in calc

[Thorniestmax]

On sheet one, I have a list of URLs, with duplicates in column B. In column J, I have dates visited.
On sheet two, I have a list of all the URLs from sheet 1, with no duplicates.

On sheet two, I want to lookup the highest date that a URL from sheet one matching the url from sheet two in one column, and the earliest date in another.
Something like:

A|B|C

URL|Most recent update|first checked
[url]|=maxif(S1.B2:B25001,a2,S1.J2:J25001)|=minif(S1.B2:B25001,a2,S1.J2:J25001)

I realize that there is no minif or maxif functions (or if there are, I haven’t found them), but if someone could show me a way to implement something that would work in the same way as they might work, I would really appreciate it.

Oh yeah: Fully updated English language Windows 7 x64 Ultimate edition with Japanese locale, using LibreOffice v4.4.4.3 Calc with en_US locale.

Had no idea what tags I was supposed to use, so please forgive me if I used the wrong ones.

[PYS]

Hi

May be a solution in MinIfMaxIf.ods

[EDIT]

I forgot some explanations:

• Most recent update (e.g. Sheet2.B2) is an array formula: needs key combination Shift+Ctrl+Enter
• To copy down with fill handle in the bottom right corner of the cell, needs to hold down the Ctrl key

Regards

[Alfe]

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

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

[erAck]

Version 4.4 is quite outdated… it might be interesting to you that some later version introduced the MAXIFS and MINIFS spreadsheet functions.

[Alfe]

I’m using Ubuntu 16.04 (i. e. the current LTS distribution) which installs Libreoffice 5.1.6.2 which still doesn’t have an MAXIFS()

[mikekaganski]

@Alfe: https://launchpad.net/~libreoffice/+archive/ubuntu/ppa

[erAck]

True, were introduced with LibreOffice 5.2. On Ubuntu you can use a PPA.

[erAck]

Version 4.4 is quite outdated… it might be interesting to you that some later version introduced the MAXIFS and MINIFS spreadsheet functions.

[Lupp]

Since V5.2 (first released week 31, 2016; see here) there are implemented the functions MAXIFS() and MINIFS(). Unfortunately there are not yet (V5.4.3) the respective offline help texts. You need to resort to the function wizard to be guided. (This actually is clear enough.)
(The mentioned functions are not yet specified in “OpenFormula”. They were introduced for better compatibility with recent versions of MS-Excel and are expected to work in the same way as SUMIFS and AVERAGEIFS concerning the conditions given as a pair of a range and a criterion each, while the range to get the data from is given as the first parameter.)