Forum Discussion

Copper Contributor

Nov 05, 2018

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...

excel

Formulas and Functions

Detlef_Lewin

Silver Contributor

Nov 05, 2018

Try SUM().

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

Enter with CTRL-SHIFT-ENTER.

Ian Dangerfield
Copper Contributor
Nov 05, 2018
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)}
- Detlef_Lewin
  Silver Contributor
  Nov 05, 2018
  Which is no surprise when you put two whole columns in the formula. Restrict it to the used range.
  - Ian Dangerfield
    Copper Contributor
    Nov 06, 2018
    =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).