formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-2226742%22%20slang%3D%22en-US%22%3Eformulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2226742%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20sumif%20formula%20needs%20to%20check%20ranges%20in%20two%20tabs%20and%20sum%20the%20values%20in%20the%20consolidated%20tab.%26nbsp%3B%20this%20used%20to%20be%20easy%20in%20older%20versions%20of%20excel.%26nbsp%3B%20Why%20is%20it%20bringing%20back%20spill%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2226742%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2226930%22%20slang%3D%22en-US%22%3ERe%3A%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2226930%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1004297%22%20target%3D%22_blank%22%3E%40Melconway2205%3C%2FA%3E%26nbsp%3BThe%20formula%20in%20B2%20will%20fill%20all%20cells%20below%20it%2C%20all%20at%20once.%20If%20it%20can't%2C%20because%20something%20else%20is%20already%20there%2C%20you'll%20get%20the%20SPILL%20error.%20Delete%20everything%20below%20(from%20B3%20and%20down).%20Then%20it%20will%20work%20as%20you%20want.%20Welcome%20to%20the%20new%20Excel%20with%20dynamic%20arrays.%20They%20are%20great!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-03-22%20at%2006.16.48.png%22%20style%3D%22width%3A%20395px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F265967i8022900E0CC1E5BC%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-03-22%20at%2006.16.48.png%22%20alt%3D%22Screenshot%202021-03-22%20at%2006.16.48.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Visitor

My sumif formula needs to check ranges in two tabs and sum the values in the consolidated tab.  this used to be easy in older versions of excel.  Why is it bringing back spill? 

2 Replies

@Melconway2205 The formula in B2 will fill all cells below it, all at once. If it can't, because something else is already there, you'll get the SPILL error. Delete everything below (from B3 and down). Then it will work as you want. Welcome to the new Excel with dynamic arrays. They are great!

Screenshot 2021-03-22 at 06.16.48.png

@Melconway2205 

 

  1. Actually you need an Array (CSE) formula rather that simple one, also you have missed to include data from Company Two.
  2. Check the formula I've used in cell B2:
{=SUMIF('COMPANY ONE'!$A$2:$A$102,'CONSOLIDATED PROFT AND LOSS'!$A$2:A138,'COMPANY ONE'!B2:B102)+SUMIF('COMPANY TWO'!$A$2:$A$37,'CONSOLIDATED PROFT AND LOSS'!$A$2:A138,'COMPANY TWO'!B2:B37)}

 

  1. Another I've solved by using built in command Data Consolidation.

Check this in E1:F138 :

Rajesh-S_0-1616392091664.png

 

N.B.  If this works for you,, then you may mark it as Best Answer as well like.