Home

SUMIFS based on two criteria with different size ranges

%3CLINGO-SUB%20id%3D%22lingo-sub-282458%22%20slang%3D%22en-US%22%3ESUMIFS%20based%20on%20two%20criteria%20with%20different%20size%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282458%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3EBefore%20you%20tell%20me%20you%20can't%20use%20SUMIFS%20with%20different%20size%20ranges%2C%20I%20know.%20I%20just%20figured%20that%20out%20which%20is%20why%20I'm%20here.%3C%2FP%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3EHere's%20the%20formula%20I%20was%20trying%20to%20use%20that%20gives%20me%20a%20%23VALUE%20error%20%3DSUMIFS(I28%3AI45%2C'MTN%20LIST'!H%3AH%2CH28%3AH45%2C'MTN%20LIST'!D%3AD%2C%22%26lt%3B%26gt%3B%22)%3C%2FP%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3EWhat%20I'm%20trying%20to%20do%20is%20sum%20the%20number%20of%20units%20of%20properties%20with%20a%20transition%20out%20date%20within%20a%20managers%20list%20of%20properties.%3C%2FP%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3ELet%20me%20break%20down%20my%20formula%3C%2FP%3E%3CUL%3E%3CLI%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3EI28%3AI45%20is%20the%20range%20containing%20the%20number%20of%20units%20from%20each%20property%20that%20a%20single%20manager%20is%20over%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3E'MTN%20LIST'!H%3AH%20is%20the%20list%20of%20ALL%20properties%20in%20the%20region%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3EH28%3AH45%20is%20the%20list%20of%20properties%20for%20a%20single%20manager%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3E'MTN%20LIST'!D%3AD%20contains%20the%20list%20of%20transition%20out%20dates%20(it%20either%20has%20a%20date%20or%20is%20blank)%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3E%22%26lt%3B%26gt%3B%22%20is%20not%20blank%3C%2FP%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3EHere's%20an%20example%20table%20to%20visualize%3C%2FP%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3ESheet%201%3C%2FP%3E%3CP%3ERalph%20the%20Manager%20%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EProperties%3A%3C%2FTD%3E%3CTD%3EUnits%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EOzark%3C%2FTD%3E%3CTD%3E264%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECanyon%20Ridge%3C%2FTD%3E%3CTD%3E276%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESkyline%20View%3C%2FTD%3E%3CTD%3E294%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%23%20of%20Units%20with%20Trans.%20Out%20Date%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3F%3F%3F%3F%3F%3F%20(Should%20be%20558)%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3EMTN%20LIST%20(sheet)%3C%2FP%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETransition%20Out%20Date%20Properties%3A%20Units%20%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E12%2F8%2F2018%3C%2FTD%3E%3CTD%3EOzark%3C%2FTD%3E%3CTD%3E264%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EWatermark%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ECanyon%20Ridge%3C%2FTD%3E%3CTD%3E276%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ESaddle%20Rock%3C%2FTD%3E%3CTD%3E500%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EVerona%3C%2FTD%3E%3CTD%3E400%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F19%2F2019%3C%2FTD%3E%3CTD%3ESkyline%20View%3C%2FTD%3E%3CTD%3E294%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3EI%20hope%20I've%20explained%20enough%20to%20help%20you%20get%20an%20idea%20of%20what%20I'm%20going%20for.%20Thanks%20in%20advance%20for%20you%20help!%3C%2FP%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3EUpdate%3A%20I%20tried%20entering%20the%20following%20formula%20as%20an%20array%20but%20it%20just%20sums%20all%20units%20not%20just%20Skyline%20View%20and%20Ozark%20like%20it%20should.%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22yklcuq-10%20hpxQMr%22%3E%3CSPAN%3E%3DSUM(SUMIFS('MTN%20LIST'!H%3AH%2C'MTN%20LIST'!D%3AD%2C%22%26lt%3B%26gt%3B%22%2C'MTN%20LIST'!E%3AE%2CH28%3AH45))%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-282458%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-282946%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIFS%20based%20on%20two%20criteria%20with%20different%20size%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282946%22%20slang%3D%22en-US%22%3E%3CP%3E%3DSUM(SUMIFS('MTN%20LIST'!H3%3AH120%2C'MTN%20LIST'!D3%3AD120%2C%22%26lt%3B%26gt%3B%22%2C'MTN%20LIST'!E3%3AE120%2CH28%3AH45))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20it%20will%20calculate%20but%20it%20returns%20the%20sum%20of%20all%20three%20properties%20(834)%20and%20not%20just%20the%20two%20that%20have%20transition%26nbsp%3Bout%20dates%20(558).%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-282626%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIFS%20based%20on%20two%20criteria%20with%20different%20size%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282626%22%20slang%3D%22en-US%22%3E%3CP%3EWhich%20is%20no%20surprise%20when%20you%20put%20two%20whole%20columns%20in%20the%20formula.%20Restrict%20it%20to%20the%20used%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-282583%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIFS%20based%20on%20two%20criteria%20with%20different%20size%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282583%22%20slang%3D%22en-US%22%3E%3CP%3EAppreciate%20the%20assist!%20Unfortunately%2C%26nbsp%3Bwhen%20I%20run%20that%20exact%20formula%20I%20get%200%20and%20when%20I%20extend%20the%20ranges%20to%20include%20all%20my%20data%20I%20get%20an%20error%20saying%2C%20%22Excel%20ran%20out%20of%20resources%20while%20attempting%20to%20calculate%20one%20or%20more%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%3DSUM((H28%3AH45%3DTRANSPOSE('MTN%20LIST'!H%3AH))*(TRANSPOSE('MTN%20LIST'!D%3AD%26lt%3B%26gt%3B%22%22))*I28%3AI45)%7D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-282529%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIFS%20based%20on%20two%20criteria%20with%20different%20size%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282529%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20SUM().%3C%2FP%3E%3CPRE%3E%7B%3DSUM((H28%3AH30%3DTRANSPOSE('MTN%20LIST'!H2%3AH7))*(TRANSPOSE('MTN%20LIST'!D2%3AD7%26lt%3B%26gt%3B%22%22))*I28%3AI30)%7D%3C%2FPRE%3E%3CP%3EEnter%20with%20CTRL-SHIFT-ENTER.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ian Dangerfield
New Contributor

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

4 Replies

Hi

 

Try SUM().

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

Enter with CTRL-SHIFT-ENTER.

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