# Obtain the latest date using criteria from another column

1. ## Obtain the latest date using criteria from another column

Good day folks,

This is my first time here and I'm hoping someone can help me. I am using Excel 2000 and Excel 365 [Work version].

I have a spread sheet which contains 3 columns as follows:

Column 1 [A] - Initials
Column 2 [B] - Date
Column 3 [C] - Volume

Column 1 also has a list of initials to select from via a drop down list, the drop down list resides in the same worksheet in column X

In columns E, F & G are a series of information pertaining to each week of the year with a Start and End Date and directly below each one is a totals volume cell coloured blue background [see attached example spread sheet]

In each of these totals volumes cells is this formulae : =SUMIF(B:B,">="&G7,C:C)-SUMIF(B:B,">"&G8,C:C)

These cells calculate the total number of records within the cell ranges specified within the date ranges within the Start and End Date cells.

This works fine, but I now have an additional criteria which I would like to add to the totals volume cells and that is as follows:

I want to only obtain the total number of reminders outstanding for the latest date for the person's initials column. That is if there is more than one entry for example for the person with the initials "HD that is within the date range for week 1 of January 2020, I want to only obtain the volume for the latest date for that individual as follows:

There are 3 entries for the initials HD in column 1 [A] I only want the G9 cell to return the figure for the latest date within the date ranges 27/12/19 to 02/01/20 inclusive and it should also calculate the volume for each of the other two initials entered, for example:

for the 5 records that are currently listed the total cell is correct in that the total is 60 based on all 5 records, however what I would like to achieve is only the latest date for the initials "HD" plus the data for initials AD & DH giving a total of 31 in the totals cell.

I can get the latest date using the LARGE function as follows: =LARGE(B:B,1) which returns the date 02/01/20, which what I want, but need to also obtain the volume associated with that date for the initials "HD" and for this scenario to apply to any other instance where a persons appear more than once within a date range.

I hope I have explained this clearly enough for someone to assist, which would be most appreciated.

P.S.

I've tried to attach a copy of the spread and a message comes back I don't have permission!!

2. ## Re: 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,

Regards

John Lee

4. ## Re: Obtain the latest date using criteria from another column

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)

5. ## Re: Obtain the latest date using criteria from another column

Hi,

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

Many thanks once again

John Lee

6. ## Re: Obtain the latest date using criteria from another column

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.

Thank you once again

John Lee

7. ## Re: Obtain the latest date using criteria from another column

Welcome to the forum

missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

8. ## Re: Obtain the latest date using criteria from another column

Hi,

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

Hopefully you can see it.

Regards

John Lee

9. ## Re: Obtain the latest date using criteria from another column

Please attach an .xlsx file - the one you have attached will not open.

10. ## Re: Obtain the latest date using criteria from another column

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

11. ## Re: Obtain the latest date using criteria from another column

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.

12. ## Re: Obtain the latest date using criteria from another column

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

13. ## Re: Obtain the latest date using criteria from another column

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.

14. ## Re: Obtain the latest date using criteria from another column

Hi,

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

15. ## Re: Obtain the latest date using criteria from another column

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.

16. ## Re: Obtain the latest date using criteria from another column

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

17. ## Re: Obtain the latest date using criteria from another column

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.

18. ## Re: Obtain the latest date using criteria from another column

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.

19. ## Re: Obtain the latest date using criteria from another column

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?

20. ## Re: Obtain the latest date using criteria from another column

Originally Posted by Bob Phillips
He tried, but he has Excel 2000, and we couldn't open it. You said so yourself.
Ah, yes - sorry!

21. ## Re: Obtain the latest date using criteria from another column

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

22. ## Re: Obtain the latest date using criteria from another column

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.

23. ## Re: Obtain the latest date using criteria from another column

Hi,

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

Regards

John Lee

24. ## Re: Obtain the latest date using criteria from another column

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1