Sumif array based on an array filter

[sauceRE]

Can't seem to figure out this formula.

I need to create a sum of the top 10 tenant rents for a property.

I was able to create a Large If formula to pull in the top 10 square footages (size) as follows:

+SUM(LARGE(IF('Rent Roll'!$G$1:$G$1000<=$E$42,IF(('Rent Roll'!$G$1:$G$1000>=$M$24)+('Rent Roll'!$K$1:$K$1000="MTM"),'Rent Roll'!$E$1:$E$1000)),ROW(INDIRECT("1:"&G47))))

Where as:
'Rent Roll'!$G$1:$G$1000<=$E$42 is all values less 12/31/16
'Rent Roll'!$G$1:$G$1000>=$M$24 is all values greater than 6/30/15
'Rent Roll'!$K$1:$K$1000="MTM" Includes month to month tenants
'Rent Roll'!$E$1:$E$1000 Is the square footage (size) of tenants
ROW(INDIRECT("1:"&G47 brings in the top X tenants; g47 set up as 10

Now, the tenants annual rents are located in 'Rent Roll'!$AC$1:$AC$1000

I can't seem to get a sumif formula to work and I think it has something to do with the array filter.

What I would like to do is be able to type in any number in G47, and it will total annual rent for the top X tenants. This way, I can simply calculate the weighted average rent for the top X tenants at any time.

Any pointers?

 

[Aladin Akyurek]

Would you re-word the problem you want to solve without the reference to the formula you posted?

 

[sauceRE]

Would you re-word the problem you want to solve without the reference to the formula you posted?

Basically, I need a sumif or sumproduct being driven by an array that includes the large function (Top X tenants) as criteria.

The previous formula posted sums the total SF of the X amount of tenants that I want to review. I would like to also drive in and total annual rents but can't figure out the formula.

 

[Aladin Akyurek]

Basically, I need a sumif or sumproduct being driven by an array that includes the large function (Top X tenants) as criteria.

The previous formula posted sums the total SF of the X amount of tenants that I want to review. I would like to also drive in and total annual rents but can't figure out the formula.

This is still formula talk. I'd suggest that you post a scaled down sample along with the result that must obtain.

 

[sauceRE]

Okay I have a sample below:

I need to sum the annual rents in column C, given the following conditions:
-Column B: Expiration date is after 6/29/15 and before 1/1/17 or MTM
-Column A: Top X tenants, where X can be controlled from another cell.
-Column A will also have duplicate values

What I would like to be able to do, is input 5 into the cell where X is located, so that I could sum the top 5 tenants rents given the conditions above.

A	B	C
Tenant Size	Expiration Date	Annual Rent
5000	6/30/2015	$100,000
5500	7/31/2015	$110,000
5500	8/31/2015	$120,000
6000	9/30/2015	$130,000
6500	12/31/2015	$140,000
7000	6/30/2016	$150,000
7500	11/30/2016	$160,000
7500	MTM	$170,000
7500	3/31/2017	$180,000

<tbody>
</tbody>

 

[Aladin Akyurek]

Must we avoid listing duplicates?

 

[sauceRE]

Must we avoid listing duplicates?

I'm just not positive how excel will interpret duplicates. If there are 15 duplicates from tenants 5-20, and I want to bring in the top 10 tenants, will excel only grab 10, or will it grab all 20? I would need it to only grab exactly 10.

FYI-
I was able to get a workaround by creating a rankif in another column that eliminated duplicate ranks and to pull in what I needed, I would like to see if it would be possible to create a formula in one cell.

 

[Aladin Akyurek]

I'm just not positive how excel will interpret duplicates. If there are 15 duplicates from tenants 5-20, and I want to bring in the top 10 tenants, will excel only grab 10, or will it grab all 20? I would need it to only grab exactly 10.

FYI-
I was able to get a workaround by creating a rankif in another column that eliminated duplicate ranks and to pull in what I needed, I would like to see if it would be possible to create a formula in one cell.

Given:

5
5
3
4

Would your top 3 be: 5, 4, 3 or 5, 5, 4?

 

[sauceRE]

5,5,4 would be the top 3

Given:

5
5
3
4

Would your top 3 be: 5, 4, 3 or 5, 5, 4?

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	Tenant Size	Expiration Date	Annual Rent		Top	5​		#List#
2​	5000​	6/30/2015​	$100,000​		After	7/15/2015​		5500​
3​	5500​	7/31/2015​	$110,000​		Before	6/30/2016​		5500​
4​	5500​	8/31/2015​	$120,000​		Include	MTM		6000​
5​	6000​	9/30/2015​	$130,000​					6500​
6​	6500​	12/31/2015​	$140,000​					7500​
7​	7000​	6/30/2016​	$150,000​
8​	7500​	11/30/2016​	$160,000​
9​	7500​	MTM	$170,000​
10​	7500​	3/31/2017​	$180,000​

H2, control+shift+enter, not just enter, and copy down:

=IF(ROWS($H$2:H2)<=$F$1,
    SMALL(IF((($B$2:$B$10>$F$2)*($B$2:$B$10<$F$3))+($B$2:$B$10=$F$4),
    $A$2:$A$10),ROWS($H$2:H2)),"")