Max If

[jr1234]

Hello,

I am trying to determine that latest Dates that occur next to a specific Name.

Here is the formula that I am using:

{=MAX(IF(POs!AB:AB=A2,POs!AC:AC))}

whereas:
POs!AB:AB is a spreadsheet range of Names
A2 is one specific Name that I want to find the latest corresponding Date
POs!AC:AC is a spreadsheet of Dates which corresponds with the Names

I cannot figure out what I am doing wrong. Any feedback?

 

[XOR LX]

Hi.

For a start, I strongly recommend that you don't use entire column references within an array formula. That formula is being forced to calculate over more than two million cells, an astonishing amount for a single formula.

Unlike e.g. COUNTIF(S)/SUMIF(S), array formulas calculate over all cells passed to them, whether technically beyond the last-used cells in those ranges or not.

Try choosing a suitably large (but not too large) upper range reference.

Regards

 

[gaz_chops]

Looks fine to me? What is it doing wrong?

 

[jr1234]

Thanks for the tip. I am now trying the following formula.

{=MAX(IF(POs!$AB$1:$AB$15000=A2,POs!$AC$1:$AC$15000))}

Unfortunately I am still getting the #VALUE! error. The PO spreadsheet has a lot of rows so I cannot narrow down the range much further.

 

[gaz_chops]

It should work? Are the dates real dates? Do the names match?

[TABLE="width: 325"]
<!--StartFragment--> <colgroup><col width="65" span="5" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65"]g[/TD]
  [TD="class: xl63, width: 65, align: right"]01/01/15[/TD]
  [TD="class: xl63, width: 65"][/TD]
  [TD="width: 65"]f[/TD]
  [TD="class: xl63, width: 65, align: right"]12/01/15[/TD]
 [/TR]
 [TR]
  [TD]g[/TD]
  [TD="class: xl63, align: right"]02/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD]a[/TD]
  [TD="class: xl63, align: right"]10/01/15[/TD]
 [/TR]
 [TR]
  [TD]g[/TD]
  [TD="class: xl63, align: right"]03/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD]g[/TD]
  [TD="class: xl63, align: right"]15/01/15[/TD]
 [/TR]
 [TR]
  [TD]g[/TD]
  [TD="class: xl63, align: right"]04/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]g[/TD]
  [TD="class: xl63, align: right"]05/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]a[/TD]
  [TD="class: xl63, align: right"]06/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]a[/TD]
  [TD="class: xl63, align: right"]07/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]a[/TD]
  [TD="class: xl63, align: right"]08/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]f[/TD]
  [TD="class: xl63, align: right"]09/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]a[/TD]
  [TD="class: xl63, align: right"]10/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]d[/TD]
  [TD="class: xl63, align: right"]11/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]f[/TD]
  [TD="class: xl63, align: right"]12/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]g[/TD]
  [TD="class: xl63, align: right"]13/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]h[/TD]
  [TD="class: xl63, align: right"]14/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]g[/TD]
  [TD="class: xl63, align: right"]15/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]d[/TD]
  [TD="class: xl63, align: right"]16/01/15[/TD]
  [TD="class: xl63"][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[XOR LX]

And are there any #VALUE! errors in either of those two ranges anywhere?

Regards

 

[jr1234]

The names do match, I made sure by using the If function to make sure two did and the result came back true. What do you mean by 'are the dates real dates?'

 

[jr1234]

I realized that I did have some #VALUE!s in my Name column - I did not realize that this would throw the whole formula off. It seems to be working now - thanks for the help!

 

[XOR LX]

You're welcome!