Forum Discussion

Ian Dangerfield's avatar
Ian Dangerfield
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(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
Ozark264
Canyon Ridge276
Skyline View294
 # of Units with Trans. Out Date
 ?????? (Should be 558)

 

MTN LIST (sheet)

 

Transition Out Date Properties: Units  

12/8/2018Ozark264 
 Watermark300 
 Canyon Ridge276 
 Saddle Rock500 
 Verona400 
1/19/2019Skyline View294 

 

 

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

excel
Formulas and Functions

4 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Nov 05, 2018

    Hi

     

    Try SUM().

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

    Enter with CTRL-SHIFT-ENTER.

    • Ian Dangerfield's avatar
      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's avatar
        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.

         

Resources