Forum Discussion
SUMIFS based on two criteria with different size ranges
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...
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)}
Which is no surprise when you put two whole columns in the formula. Restrict it to the used range.
=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).