SUMIFS based on two criteria with different size ranges

[Ian Dangerfield]

Before you tell me you can't use SUMIFS with different size ranges, I know. I just figured that out which is why I'm here.

Here's the formula I was trying to use that gives me a #VALUE error =SUMIFS(I28:I45,'MTN LIST'!H:H,H28:H45,'MTN LIST'!D:D,"<>")

What I'm trying to do is sum the number of units of properties with a transition out date within a managers list of properties.

Let me break down my formula

• I28:I45 is the range containing the number of units from each property that a single manager is over

• 'MTN LIST'!H:H is the list of ALL properties in the region

• H28:H45 is the list of properties for a single manager

• 'MTN LIST'!D:D contains the list of transition out dates (it either has a date or is blank)

• "<>" is not blank

Here's an example table to visualize

Sheet 1

Ralph the Manager  

Properties:	Units
Ozark	264
Canyon Ridge	276
Skyline View	294
	# of Units with Trans. Out Date
	?????? (Should be 558)

MTN LIST (sheet)

Transition Out Date Properties: Units  

12/8/2018	Ozark	264
	Watermark	300
	Canyon Ridge	276
	Saddle Rock	500
	Verona	400
1/19/2019	Skyline View	294

I hope I've explained enough to help you get an idea of what I'm going for. Thanks in advance for you help!

Update: I tried entering the following formula as an array but it just sums all units not just Skyline View and Ozark like it should. 

=SUM(SUMIFS('MTN LIST'!H:H,'MTN LIST'!D:D,"<>",'MTN LIST'!E:E,H28:H45))

[#2]

Hi

Try SUM().

{=SUM((H28:H30=TRANSPOSE('MTN LIST'!H2:H7))*(TRANSPOSE('MTN LIST'!D2:D7<>""))*I28:I30)}

Enter with CTRL-SHIFT-ENTER.

[#3]

Appreciate the assist! Unfortunately, when I run that exact formula I get 0 and when I extend the ranges to include all my data I get an error saying, "Excel ran out of resources while attempting to calculate one or more formulas.

{=SUM((H28:H45=TRANSPOSE('MTN LIST'!H:H))*(TRANSPOSE('MTN LIST'!D:D<>""))*I28:I45)}

[#4]

Which is no surprise when you put two whole columns in the formula. Restrict it to the used range.

[#5]

=SUM(SUMIFS('MTN LIST'!H3:H120,'MTN LIST'!D3:D120,"<>",'MTN LIST'!E3:E120,H28:H45))

Now it will calculate but it returns the sum of all three properties (834) and not just the two that have transition out dates (558).