Nov 05 2018 09:39 AM
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))
Nov 05 2018 11:40 AM
Hi
Try SUM().
{=SUM((H28:H30=TRANSPOSE('MTN LIST'!H2:H7))*(TRANSPOSE('MTN LIST'!D2:D7<>""))*I28:I30)}
Enter with CTRL-SHIFT-ENTER.
Nov 05 2018 01:14 PM
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)}
Nov 05 2018 02:51 PM
Which is no surprise when you put two whole columns in the formula. Restrict it to the used range.
Nov 06 2018 08:01 AM
=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).