Obtain the latest date using criteria from another column

IN week 2, would you want RL and NH for the 8th, but LW for the 9th?

Hi,

Thank you for your response, the answer is yes I would.

Regards

John Lee

That makes it tricky. I would add a helper column, with this formula in D3 copied down

=B3=MAX(($A$2:$A$20=A3)*(B3<=B3+8-WEEKDAY(B3+2)-1)*($B$2:$B$20))

an then this formula for the SUM

=SUMPRODUCT(--($B$3:$B$20>=G7)*($B$3:$B$20<=G8),--($D$3:$D$20),$C$3:$C$20)

Hi,

Thank you, I will deploy what you've suggested and let you know how I get on.

Many thanks once again

John Lee

Hi,

I deployed your suggested formulae, unfortunately the sum value still returned 60 and not 45. I wanted to obtained the latest volume for the latest date in each case and sum those values up, which in this case should have been 45 for the following dates 28, 29 Dec and 2 x 02 Jan. 7, 9, 14 & 15 = 45 thus excluding the 27 Dec value of 15.

All the values in column D are showing as True

I placed the =SUMPRODUCT(--($B$3:$B$20>=G7)*($B$3:$B$20<=G8),--($D$3:$D$20),$C$3:$C$20) formulae in the adjacent cell to G9 to compare because you have absolute references.

Any additional suggestions most welcome.

Thank you once again

John Lee

Hi,

I've just got a message on how to upload my spread sheet, here goes

attachment added.

Hopefully you can see it.

Regards

John Lee

Hi,

It's an excel 2000 file, unfortunately I don't have 365 on my home computer, I usually convert the files when I'm at my place of work. but no problem, thanks for your assistance so far.

Regards

John Lee

I forgot to mention that the first formula is an array formula, commit it with Ctrl-Shift-Enter, not just Enter.

Don't understand what you are saying about the SUMPRODUCT formula.

Hi,

The SUMPRODUCT formulae is adding the 3 records with initials HD together, what I actually need is for that to obtain only the volume with the latest date within the two date criteria, and to add all the other records within that same date criteria together, so instead of returning a total value of 36 for the 3 records associated with the Initials "HD" I need it to return a total value consisting of the other two records that fall within the date criteria plus the record with the latest date for the initials HD and that value should be 38 [consisting of 9 for initials DH, 15 for initials AD and 14 for initials HD]. So the other two records associated with the initials HD are excluded totally.

The whole idea behind this is to always grab the latest date for each individual each week, no matter how many entries they make for that week [should not be more than 5 entries though, but you know people] and to add only the records with the latest date together to produce a weekly record of the total number of reminder records out of date.

I hope this make sense. Thanks once again.

Regards

John Lee

No it isn't, it is adding the second HD, the AD, and the DH. If you array-enter the first formula as I said in my follow-up, you will see that. I know what the idea is, my first question to you made that plain.

Hi,

Please see attached screen shot with your formulae entered as directed.

If you add up the 3 records within the date criteria you will see as shown within the spread sheet that they add up to 36 whereas the other 2 records plus the latest date record for HD add up to 38, hence why I have made my observation accordingly.

Regards

John Lee

It's the true/false formula that I not working for you. Show me a picture with the formula in D3 in the formula bar.

Hi,

Picture uploaded, please note that the reference within your formulae =B3=MAX(($A$2:$A$20=A3) had to be altered to the absolute reference of where the Initials list is located within the spread sheet which is column X row 3 so I altered the A3 to $X$3 because as it is you get a TRUE return for all in column D as you can see. When I changed the reference as stated then you get TRUE only for those records where the initials HD appear within the date criteria.

Regards

John Lee

Originally Posted by AliGW

Please see the instructions in the banner at the top of the page telling you how to attach the workbook itself. We can't work properly with a picture.

He tried, but he has Excel 2000, and we couldn't open it. You said so yourself.

That is odd, the formula looks the same as mine, and mine works. In the picture, the formula is not array-entered, can you re-do it and post the picture?

Hi,

I've re copied and pasted exactly as per your directions, note that with the curly brackets in place in the formulae bar I get a #VALUE error, when I remove the curly brackets I don't get the #VALUE error.

I can upload the spread sheet again if you like, I don't what version of excel your using but the version I use at work allows me to convert my Excel 2000 files for use in 365.

Regards

John Lee

I don't think I can help further until I get a copy of the workbook. Everything is fine here, so you have some condition/situation that I don't.

Hi,

No problem thank you for your assistance so far, have a good new year.

Regards

John Lee

Wrong answer John Lee. Why don't you post the workbook on one of the file sharing sites, it should be an easy fix.