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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Tenant Size[/TD]
[TD]Expiration Date[/TD]
[TD]Annual Rent[/TD]
[/TR]
[TR]
[TD]5000[/TD]
[TD]6/30/2015[/TD]
[TD]$100,000[/TD]
[/TR]
[TR]
[TD]5500[/TD]
[TD]7/31/2015[/TD]
[TD]$110,000[/TD]
[/TR]
[TR]
[TD]5500[/TD]
[TD]8/31/2015[/TD]
[TD]$120,000[/TD]
[/TR]
[TR]
[TD]6000[/TD]
[TD]9/30/2015[/TD]
[TD]$130,000[/TD]
[/TR]
[TR]
[TD]6500[/TD]
[TD]12/31/2015[/TD]
[TD]$140,000[/TD]
[/TR]
[TR]
[TD]7000[/TD]
[TD]6/30/2016[/TD]
[TD]$150,000[/TD]
[/TR]
[TR]
[TD]7500[/TD]
[TD]11/30/2016[/TD]
[TD]$160,000[/TD]
[/TR]
[TR]
[TD]7500[/TD]
[TD]MTM[/TD]
[TD]$170,000[/TD]
[/TR]
[TR]
[TD]7500[/TD]
[TD]3/31/2017[/TD]
[TD]$180,000[/TD]
[/TR]
</tbody>[/TABLE]

 

[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]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]Tenant Size[/td][td]Expiration Date[/td][td]Annual Rent[/td][td][/td][td]Top[/td][td]

5​

[/td][td][/td][td]#List#[/td][/tr]

[tr][td]

2​

[/td][td]

5000​

[/td][td]

6/30/2015​

[/td][td]

$100,000​

[/td][td][/td][td]After[/td][td]

7/15/2015​

[/td][td][/td][td]

5500​

[/td][/tr]

[tr][td]

3​

[/td][td]

5500​

[/td][td]

7/31/2015​

[/td][td]

$110,000​

[/td][td][/td][td]Before[/td][td]

6/30/2016​

[/td][td][/td][td]

5500​

[/td][/tr]

[tr][td]

4​

[/td][td]

5500​

[/td][td]

8/31/2015​

[/td][td]

$120,000​

[/td][td][/td][td]Include[/td][td]MTM[/td][td][/td][td]

6000​

[/td][/tr]

[tr][td]

5​

[/td][td]

6000​

[/td][td]

9/30/2015​

[/td][td]

$130,000​

[/td][td][/td][td][/td][td][/td][td][/td][td]

6500​

[/td][/tr]

[tr][td]

6​

[/td][td]

6500​

[/td][td]

12/31/2015​

[/td][td]

$140,000​

[/td][td][/td][td][/td][td][/td][td][/td][td]

7500​

[/td][/tr]

[tr][td]

7​

[/td][td]

7000​

[/td][td]

6/30/2016​

[/td][td]

$150,000​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

7500​

[/td][td]

11/30/2016​

[/td][td]

$160,000​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

7500​

[/td][td]MTM[/td][td]

$170,000​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

7500​

[/td][td]

3/31/2017​

[/td][td]

$180,000​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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)),"")