Finding max of a range that fits a specific criteria like "MAXIF"

Hi Ian,

I think that still wouldn’t work because I could probably only do one at a time? I need all of them simultaneously represented on a second table.

After reading through lots of threads on this issue, it’s pretty clear that there’s no good way to do this, which is a bit disappointing, because it would be very simple in Excel (I’m also lamenting the lack of CSE functions). However, I think I have found a way using IF/COUNTIF, followed by CONCATENATE, then VLOOKUP, which is a bit cumbersome, but will work.

The only problem I’m having is that COUNTIF doesn’t seem to be outputting the correct value for a few of my documents, but since the formula works for the other 90%, I think this must be something specific to those references and not an issue with the formula, so I think I'm good, I just need to go back over it to figure out what's wrong with the entries for these documents.

Thanks, but that won't work for me, because I have hundreds of documents, meaning I would have to add way too many columns to my spreadsheet. If that's the only solution, it would be better for me to manually update the last date modified (which I really rather not have to do).

I know I can sort the table, the point is I need the date to appear on a different table that has different summary information.

I rather not have my formula be dependent on how the table is sorted since it will be useful to be able to change the sorting without messing up my other table.

What I did was an adaptation of what is suggested in this discussion:

need to simulate MAXIF.

You can ignore columns D and F. In Column B I wrote a formula using IF and COUNTIF to return "R" for the most recent date of each document. Then in Column A I use CONCATENATE to combine Columns C and B to create a unique name for each entry with a most recent date. On my other table I can now write a formula like =VLOOKUP("DOC H3 WR,TABLE1::A:E,5) which will return 8/7/17. When I add a newer date "R" will move and so will the reference.

I am having a small problem. If you look at the two rows I bolded, Column B should have returned "R", however it is blank. This is because COUNTIF is returning 2 rather than 1. I think it might have something to do with the custom date formats I am using, because If I change it to Automatic it works. The weird thing is that I have used the same formats throughout the spreadsheet and sometimes they work and sometimes they don't. There's no correlation between the format and whether or not it works (the screenshot is only a small part of the data, all three formats have cases where they work and cases where they don't).

Hypothetically that should work and is a bit simpler. However, in practice it is returning more incorrect values than my other formula. I think the problem must somehow be how the dates are stored in my document, so I'm going to try to mess around with that and see if I can fix whatever is causing COUNTIF to read them wrong.

Thanks.

They're incorrect because I can see that they are wrong by doing the calculation myself. For example it might return a 0 for a row dated 12/10/16 when there is another row dated 12/16/16 that fits the same criteria for Column A (therefore should have returned a 1).

Like I said, I don't think the problem is your formula or my formula. The problem seems to be how I have entered the dates into the document. It doesn't seem to like that I have mixed multiple formats and might not be reading all of them as dates and times. I'm working on fixing that now. Once I get that figured out, both formulas should work perfectly.

The document names are all correct and I’m pretty sure all of the entries were proper dates, since there was no problem with sorting, and when I copy/pasted, then reformatted, everything (even the times) was exactly right.

I’m not entirely sure, but it seems the issue is how COUNTIF reads cell contents. If I have a cell whose value is 12/10/16, but I have it formatted as Dec 2016, I think COUNTIF reads it as “Dec 2016”, so the different formats mess it up and your formula sent back lots of “incorrect” values. My formula worked a bit better because I had already partially fixed the problem using DATEVALUE. However, DATEVALUE reads the cell that is formatted Dec 2016 as “12/1/16” not “12/10/16”, so in some cases I still had problems. It couldn’t recognize 12/16/16 as bigger than 12/10/16.

I have reasons for wanting to keep my formatting, but I created another column with uniform formatting to work around this. If I use this new column as the reference, both COUNTIF formulas work properly.

All I did was copy/paste all my dates to a new column and change the formatting from the various custom formats I had (2016, Dec 2016, 12 10 2016) to a single format (12/10/2016 0:00:00). I did not change any of the date or time values at all, so if COUNTIF reads the cell values, I have no idea why it wouldn’t read the first column but doesn’t have a problem with the new one, because the values are exactly the same. I guess why doesn’t really matter since there was an easy fix, but it would sort of be nice to understand.