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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies