ADDING CELL BASED ON LATEST DATE WITH DATA

[rsd007]

Hi,

I am just new to the Forum, looking for some help.
I have Data in multiple column in Excel2010.
Column A is all Date (some of them are repeated)
Column B & C has Names and other Data.
Column D has Values in some and some are empty.
I am looking for help to make a simple formula to find the max Date with value in Column D and if we have multiple cell with Max date and different values will like to Add them.

I will really appreciate all the help I will get and Thanks.

 

[Peter Bartholomew]

@rsd007
First. I am sure the ninjas would wish to welcome you to the forum.
Secondly, it would help if you were to upload a sample file to explain you problem and help others work on it for you.

By way of warning, the way in which I approach a problem might not suit you, either as a work practice or because of the release of Office that you use. If so, there are others whose methods may suit you better.

I would use the MAXIFS function to determine the last date associated with the name. Assuming the data is held in a Table
= MAXIFS(Table1[date], Table1[name], name, Table1[value], "<>" )

The total value for that name and date is then given by
= SUMIFS( Table1[value], Table1[name], name, Table1[date], lastDate )

 

[vletm]

rsd007
As You named Excel2010-version.
Try to test this way...
F-column has notes.
Q: What would that 'Add them' mean?

 

[Peter Bartholomew]

@vletm
Thanks for chipping in. I had overlooked the fact that the OP had specified Excel 2010. Based on your file, I have added the condition that the name must match a specified string and interpreted the Sum as the sum of values rather than count of records.

 

[rsd007]

Thank you Everyone for the reply and the help.
Peter,
Is it possible to get same value without creating the table I have Data in full sheet and is growing?
Thanks again for the support.

 

[Peter Bartholomew]

Yes, it is possible; whether it is desirable is another matter. Tables were only introduced with Office 2007 (I think based upon lists that had been around a little longer). They represented a huge step forward in dealing with growing datasets in that they provide a structured means of referencing data dynamically as it grows.

If you go back further in time there are techniques which identify the extent of data by searching for the end or by counting the terms. Also some formulas work with full column references such as A:A [others try to process all million or so rows and take forever to evaluate].

@vletm or @bosco_yip might be better placed to assist rather than my making recommendations concerning methods I would not use.

If there are specific issues you have in using Tables or defined names then by all means ask.

 

[vletm]

rsd007
Same without table...
I already asked: What would that 'Add them' mean?
Many things matter ... this works with my file and data.

 

[Peter Bartholomew]

@rsd007
Before signing off, I thought I would leave you a version of my original attempt to address what I understood to be your problem but, this time, aimed at Excel 2010. The MAXIFS(…) have been replaced by nested functions MAX(IF(…)).

vletm's solution is interesting it that it contains whole-column references and filters; techniques that I have never used! I suggest you should provide an example describing the result you seek though, if you are to get a solution that you can use.

 

[vletm]

Peter Bartholomew
... before rsd007 would give needed information,
it's a challenge to give other version.
eg term full sheet would mean a lot of rows data or ~30.

rsd007
You do not need any formula...
1) select columns from A to D > Do Filter
2) filter D-column as 'Greater than 0'
3) sort A-column as 'Ascending'
4) You'll get 'maxDate-values' in top

 

[rsd007]

Thanks for showing direction I got it working.