MIN & MAX Formula with Multiple Variables

[marko0223]

I am trying to get the MIN loan amount and the MAX loan amount in the data I have. I need to find them within a date range and I need the Freddie and Fannie loans as one group and FHA loans in their own group within the same date range. Some of the FHA loans are on 2 different sheets. Is there a way to do this? If this doesn't make sense I can try and clarify. Here is an example of the data:


Sheet 1

1/5/19 Fannie $1,000,000.00
1/6/19 Freddie $8,000,000.00
1/7/19 Fannie $4,000,000.00
1/7/19 FHA $10,000,000.00

Sheet 2

1/9/19 FHA $11,000,000.00
1/11/19 FHA $3,000,000.00


Sheet 3

This is where my formula is going.

 

[marko0223]

Can anyone help me with this?

I am trying to get the MIN loan amount and the MAX loan amount in the data I have. I need to find them within a date range and I need the Freddie and Fannie loans as one group and FHA loans in their own group within the same date range. Some of the FHA loans are on 2 different sheets. Is there a way to do this? If this doesn't make sense I can try and clarify. Here is an example of the data:


Sheet 1

1/5/19 Fannie $1,000,000.00
1/6/19 Freddie $8,000,000.00
1/7/19 Fannie $4,000,000.00
1/7/19 FHA $10,000,000.00

Sheet 2

1/9/19 FHA $11,000,000.00
1/11/19 FHA $3,000,000.00


Sheet 3

This is where my formula is going.

 

[steve the fish]

Do you have maxifs available in your excel version? What are the date ranges? Are they typed in cells or being hardcoded?

 

[marko0223]

I do not have maxifs unfortunately. The date range is from 1/1/17 to current day. I have the beginning and end date in their own cells that I can change depending on what months I need the information from.

Do you have maxifs available in your excel version? What are the date ranges? Are they typed in cells or being hardcoded?

 

[marko0223]

Can anyone help me find a solution? I would greatly appreciate it!

 

[Marcelo Branco]

Could you provide the expected results and how do you want them displayed in Sheet3?

M.

 

[marko0223]

On Sheet 3 I just want the highest loan amount and the lowest loan amount displayed. So Sheet 3 would look like:

Highest Loan Amount(Fannie & Freddie) $8,000,000.00 Lowest Loan Amount(Fannie and Freddie) $1,000,000.00

Highest Loan Amount (FHA) $11,000,000.00 Lowest Loan Amount (FHA) $3,000,000.00

 

[Marcelo Branco]

Are the dates m/d/yy or d/m/yy?


M.

 

[marko0223]

They are m/d/yy.

 

[Marcelo Branco]

Maybe something like this

Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Date​

[/TD]
[TD]

Name​

[/TD]
[TD]

Value​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1/5/19​

[/TD]
[TD]

Fannie​

[/TD]
[TD]

1000000,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1/6/19​

[/TD]
[TD]

Freddie​

[/TD]
[TD]

8000000,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

1/7/19​

[/TD]
[TD]

Fannie​

[/TD]
[TD]

4000000,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

1/7/19​

[/TD]
[TD]

FHA​

[/TD]
[TD]

1000000,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Date​

[/TD]
[TD]

Name​

[/TD]
[TD]

Value​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1/9/19​

[/TD]
[TD]

FHA​

[/TD]
[TD]

11000000,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1/11/19​

[/TD]
[TD]

FHA​

[/TD]
[TD]

3000000,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet3

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Name​

[/TD]
[TD]

Min​

[/TD]
[TD]

Max​

[/TD]
[TD][/TD]
[TD]

StartDate​

[/TD]
[TD]

EndDate​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Fannie & Freddie​

[/TD]
[TD="bgcolor: #D9D9D9"]

1000000,00​

[/TD]
[TD="bgcolor: #D9D9D9"]

8000000,00​

[/TD]
[TD][/TD]
[TD]

1/1/17​

[/TD]
[TD]

8/8/19​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

FHA​

[/TD]
[TD="bgcolor: #D9D9D9"]

1000000,00​

[/TD]
[TD="bgcolor: #D9D9D9"]

11000000,00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


F2
=TODAY()

Array formula in B2
=MIN(IF(Sheet1!A2:A100>=E2,IF(Sheet1!A2:A100<=F2,IF(ISNUMBER(SEARCH(Sheet1!B2:B100,A2)),Sheet1!C2:C100))))
confirmed with Ctrl+Shift+Enter, not just Enter

Array formula in C2
=MAX(IF(Sheet1!A2:A100>=E2,IF(Sheet1!A2:A100<=F2,IF(ISNUMBER(SEARCH(Sheet1!B2:B100,A2)),Sheet1!C2:C100))))
confirmed with Ctrl+Shift+Enter, not just Enter

Array formula in B3
=MIN(MIN(IF(Sheet1!A2:A100>=E2,IF(Sheet1!A2:A100<=F2,IF(Sheet1!B2:B100=A3,Sheet1!C2:C100)))),MIN(IF(Sheet2!A2:A100>=E2,IF(Sheet2!A2:A100<=F2,IF(Sheet2!B2:B100=A3,Sheet2!C2:C100)))))
confirmed with Ctrl+Shift+Enter, not just Enter

Array formula in C3
=MAX(MAX(IF(Sheet1!A2:A100>=E2,IF(Sheet1!A2:A100<=F2,IF(Sheet1!B2:B100=A3,Sheet1!C2:C100)))),MAX(IF(Sheet2!A2:A100>=E2,IF(Sheet2!A2:A100<=F2,IF(Sheet2!B2:B100=A3,Sheet2!C2:C100)))))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.