Finding MIN() date in column of adjoining worksheet 1

[BugZap13]

I have a spreadsheet with a summary worksheet with subsequent data worksheets for various investments, 1 sheet for each investment. On the summary sheet I am trying to find the MIN() date of a column on the data sheet but only if a cell next to the date has a value. Below is a sample of the data sheet. I want to pick up the MIN(Check Date) if the "Investment" column has a value. I also want to pick up MIN(Check Date) if the payment column has a value. Each month a new row is added to the data sheets so the formula needs to work on the entire column.

........  [u]Column A[/u]       [u]Column B[/u]       [u]Column C[/u]
[u]Row 5[/u]     Check Date     Investment     Payment
[u]Row 6[/u]     3/24/2021      $1,000.00
[u]Row 7[/u]     8/10/2021                     $26.90
[u]Row 8[/u]     8/25/2021                     $33.35
[u]Row 9[/u]     9/25/2021                     $38.19

TIA, Mark

 

[Andrzejek]

>I want to pick up the MIN(Check Date) if the "Investment" column has a value.
>I also want to pick up MIN(Check Date) if the payment column has a value

Could you clarify what you mean by "pick up"? Highlight? Display in column D?
A sample of the output would be nice...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Hi,

...subsequent data worksheets for various investments, 1 sheet for each investment.

It's a huge mistake having your data chopped up into multiple sheets.

To get the min value, use the MINIFS() function.

To leverage Excel's powerful table features, this table ought to be a Structured Table. Adding rows in a Structured Table AUTOMATICALLY propagates formulas to that added row.

We need the answer to Andy's questions.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[mintjulep]

>I want to pick up the MIN(Check Date) if the "Investment" column has a value.
>I also want to pick up MIN(Check Date) if the payment column has a value

Column A has a min independent of the "Investment" and "Payment" columns.

It is not clear what you are trying to do, but it is almost certainly less complicated than you are trying to make it.

 

[BugZap13]

The summary sheet would look something like the following based on the initial data sheet:

Invest Date	First Payment 	Last Payment
3/24/2021       8/10/2021       9/25/2021

The column above "Invest Date" is MIN("Check Date") where "Investment" <> 0
The column above "First Payment" is MIN("Check Date") where "Payment" <> 0
The column above "Last Payment" is MAX("Check Date") where "Payment" <> 0

HTH, Mark

 

[SkipVought]

What are you referring to? I thought you wanted the minimum date for each of Investment and Payment.

Did you try MINIFS()?

The questions posed to you was where do you want the MIN date values.

I kinda thought this might be a contender for your unstated requirement...

[pre]
First 3/24/21 8/10/21
Last 9/25/21
Check Date Investment Payment
3/24/21 $1,000.00
8/10/21 $26.90
8/25/21 $33.35
9/25/21 $38.19
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[BugZap13]

Tried the following wanting the minimum date value from column A but only if the Investment in column B is > 0. 'BP-101' is the sheet name, "A:A" is the "Check Date" column, "B:B" is the "Investment" column.

=minfs('BP-101'!A:A,'BP-101'!B:B,0)

Couple of problems.
Assuming =MINFS(MinRange,CriteriaRange,Criteria)

1. I am not sure for the Criteria how to check if CriteriaRange > 0. It appears the MINFS() function wants to check for a specific value.​

2. I do NOT want to evaluate rows within CriteriaRange that are NULL or not numeric.​

 

[SkipVought]

Your CRITERIA should be ">0"

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[BugZap13]

Tried that but EXCEL pops up "There's a problem with this formula...." when I add the greater than sign.

 

[SkipVought]

Are you using QUOTES? Standard operating procedure using [tt]...IF.()[/tt] functions

Your CRITERIA should be ">0" LITERALLY!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

If there is "1 sheet for each investment" and (I assume) the new data is entered on the bottom of the previous data, then there should be just one value for "Investment" in column B with nothing in column C for that row, with similar/reverse logic for "Payment" in column C

Then, B1 should always have a simple formula of "=A6"
C1 should have "=A7", and C2 should 'pick up' the last value from column A below A6

[pre]
A B C
1 First 3/24/21 8/10/2021
2 Last 9/25/2021
3
4
5 Check Date Investment Payment
6 3/24/2021 $1,000.00
7 8/10/2021 $26.90
8 8/25/2021 $33.35
9 9/25/2021 $38.19 [/pre]

Or, am I missing something here....

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

BZ, you haven't been very forthcoming with any clear, concise and complete explanation of what you're trying to accomplish. So I made some assumptions, realizing that when I ass-u-me, well, you know the rest.

First, I asserted right up front that you've made a huge blunder by chopping your data up into multiple sheets. It may "seem so right," but it makes data analysis so difficult. So here's my guess at what your data, consolidated into one table (referred to as Table2) might look like...

[pre]
Investment Date Amount Inv/Pmt
Inv01 3/1/2021 $1,000.00 Inv
Inv01 6/5/2021 $67.45 Pmt
Inv01 7/8/2021 $73.00 Pmt
Inv01 8/23/2021 $500.00 Inv
Inv01 9/14/2021 $106.50 Pmt
Inv02 3/15/2021 $850.00 Inv
Inv02 5/23/2021 $164.80 Pmt
Inv02 7/17/2021 $400.00 Inv
Inv02 8/22/2021 $25.76 Pmt
[/pre]

And now the Summary sheet guess...

[pre]
Investment First Inv First Pmt Last Pmt
Inv01 3/1/21 6/5/21 9/14/21
Inv02 3/15/21 5/23/21 8/22/21
[/pre]

And my formulas using Structured Table notation-by-Selection...
Note: Column A can be generated by a simple query using MS Query.
[tt]
B2: =MINIFS(Table2[Date],Table2[Amount],">0",Table2[Inv/Pmt],"Inv",Table2[Investment],$A2)
C2: =MINIFS(Table2[Date],Table2[Amount],">0",Table2[Inv/Pmt],"Pmt",Table2[Investment],$A2)
D2: =MAXIFS(Table2[Date],Table2[Amount],">0",Table2[Inv/Pmt],"Pmt",Table2[Investment],$A2)
[/tt]

If you ADD more Investments, the formulas above will propagate each time you Refresh the query and very possibly, the other summary columns in the table can ALL be accomplished in the query itself, sans spreadsheet functions.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

Does it mean that for [tt]Inv01[/tt] initially we invested $1,000.00 and then added another $500.00 (on 8/23/2021) ?
Similarly for [tt]Inv02[/tt]: $850.00 plus $400.00
In that case the Summary sheet should have some additional information, like the Investment Total.

You are right, Skip. Without "any clear, concise and complete" specifications, all we can do is guess... :-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[BugZap13]

Skip thanx for your example. There is more to the problem then was presented and if I were getting the data table from a database I think your solution would be the way to go. One sheet containing the data from all investments and one summary page like you illustrated. Sorry for the confusion in the way I presented the problem.

It turns out my main problem was not enclosing the criteria in quotes as you pointed out in your prior post. Poor assumption on my part thinking the criteria type should match the cells you are comparing. The examples I found were comparing strings and made sense that they were enclosed in quotes. Since I was comparing numbers I removed the quotes. The programmer in me got in the way.

The solution was as follows:

=MINIFS('BP-101'!A:A,'BP-101'!B:B,">0")​

 

[SkipVought]

Note that my Structured Table "database" is on a separate sheet from the Summary and that the notation is much more descriptive than A1 notation.

If you're getting data from different investments and therefore putting them in different sheets, I'd still CONSOLIDATE to ONE sheet/table. You'll save yourself a lot of heartache especially if you ever need to do investment-to-investment analysis or any kind supra-investment analysis.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!