Extracting the newest date based on 2 criteria

[ambepat]

Hi Guys,

I am trying to extract records for the newest date within a set of data that meets 2 criteria.

I have tried this array formula below but it returns a #VALUE error.

Here is the formula I have tried. For some reason it is not working and I need to understand why
=LARGE(IF($L$2:$L$671=$J2,IF($D$2:$D$6584=N$1,$G$2:$G$6584)),1)

Any help will be appreciated.

Thanks,

Patrick

 

[ambepat]

Hi Guys,

I am trying to extract records for the newest date within a set of data that meets 2 criteria.

I have tried this array formula below but it returns a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] error.

Here is the formula I have tried. For some reason it is not working and I need to understand why
=LARGE(IF($L$2:$L$6584=$J2,IF($D$2:$D$6584=N$1,$G$2:$G$6584)),1)

Any help will be appreciated.

Thanks,

Patrick

Actually the correct formula is this =LARGE(IF($L$2:$L$6584=$J2,IF($D$2:$D$6584=N$1,$G$2:$G$6584)),1). Made sure the row numbers are the same but still shows the #VALUE error.

 

[ambepat]

Is there any one that can help me with the above problem please?

 

[alansidman]

Look at this link for the MaxIf function

http://www.contextures.com/excelminmaxfunction.html

 

[Aladin Akyurek]

Hi Guys,

I am trying to extract records for the newest date within a set of data that meets 2 criteria.

I have tried this array formula below but it returns a #VALUE error.

Here is the formula I have tried. For some reason it is not working and I need to understand why
=LARGE(IF($L$2:$L$671=$J2,IF($D$2:$D$6584=N$1,$G$2:$G$6584)),1)

Any help will be appreciated.

Thanks,

Patrick

The ranges the formula refers to are unequal.

By the way, LARGE(X,1) is the same as MAX(X).

 

[ambepat]

The ranges the formula refers to are unequal.

By the way, LARGE(X,1) is the same as MAX(X).

Hi Aladin,

Thanks for your reply. I have fixed the ranges and also used both Max and LARGE but I still get the same #Value error. I just can't seem to know why. Been at this for about 2 days now and I just can't understand why it is not working. Any help will be appreciated.

P

 

[Aladin Akyurek]

Hi Aladin,

Thanks for your reply. I have fixed the ranges and also used both Max and LARGE but I still get the same #Value error. I just can't seem to know why. Been at this for about 2 days now and I just can't understand why it is not working. Any help will be appreciated.

P

Either...

=MAX(IF($L$2:$L$671=$J2,IF($D$2:$D$671=N$1,$G$2:$G$671)))

Or...

=MAX(IF($L$2:$L$6584=$J2,IF($D$2:$D$6584=N$1,$G$2:$G$6584)))

should work. Such formulas require that you confirm them with control+shif+enter, not just with enter.

Control+shift+enter means: Press down the control and the shift keys at the same while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition, visible on the formula bar.

If your Excel knows MAXIFS function, you can just enter:

=MAXIFS($G$2:$G$6584,$L$2:$L$6584,$J2,$D$2:$D$6584,N$1)

 

[ambepat]

Either...

=MAX(IF($L$2:$L$671=$J2,IF($D$2:$D$671=N$1,$G$2:$G$671)))

Or...

=MAX(IF($L$2:$L$6584=$J2,IF($D$2:$D$6584=N$1,$G$2:$G$6584)))

should work. Such formulas require that you confirm them with control+shif+enter, not just with enter.

Control+shift+enter means: Press down the control and the shift keys at the same while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition, visible on the formula bar.

If your Excel knows MAXIFS function, you can just enter:

=MAXIFS($G$2:$G$6584,$L$2:$L$6584,$J2,$D$2:$D$6584,N$1)

Hi Aladin,

Thanks for your reply. This is what is baffling me. I have been at this for 2 days and I know the formula should work but it is still giving me a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] error.

So I don't know if it is because it is from the data.

I have just tried the formulas you gave

=MAX(IF('Data - PMC'!$U$2:$U$6584='Service Matrix'!$C18,IF('Data - PMC'!$H$2:$H$6584='Service Matrix'!AW$17,'Data - PMC'!$M$2:$M$6584)))

but it shows a value error. Could duplicates have anything to do with this. What do you think could cause this [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] error? I have tried to evaluate the formula and I can see it has the array value in it but when it goes to do the max it just changes to [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] error. Just can't get my head round it.

 

[Aladin Akyurek]

What we get with the following formula?

=COUNT('Data - PMC'!$M$2:$M$6584)

Do we have formulas in any of the ranges the formula refers to?

 

[ambepat]

What we get with the following formula?

=COUNT('Data - PMC'!$M$2:$M$6584)

Do we have formulas in any of the ranges the formula refers to?

We get 6524