sum if dynamic range

%3CLINGO-SUB%20id%3D%22lingo-sub-1408183%22%20slang%3D%22en-US%22%3Esum%20if%20dynamic%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1408183%22%20slang%3D%22en-US%22%3E%3CP%3Esee%20attached%20file.%26nbsp%3B%20Col%20i%20is%20either%20debit%20or%20credit.%26nbsp%3B%20col%20j%20is%20an%20amount.%26nbsp%3B%20in%20cell%20s46%20i%20have%20this%20formula%3C%2FP%3E%3CP%3E%3CSTRONG%3Esumif(%24I%2415%3AI40%2Ccredit%2CCash_Advance_Amount)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3Bi%20have%20named%20the%20range%20%22debit%22%20as%20q46%3C%2FP%3E%3CP%3ECash_Advance_Amount%20is%20another%20named%20range.%26nbsp%3B%20initially%20i%20named%20range%20%24J%2415%3A%24J%2421%20as%20Amount.%26nbsp%3B%20then%20i%20went%20to%20the%20Name%20Manager%20and%20edited%20this%20.%20now%20the%20%3CEM%3ERefers%20To%3C%2FEM%3E%20section%20has%20this%20formula%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3Doffset('Cash%20Advance-Bugando%20MC'!%24J%241%2C0%2C0%2CCOUNTA('Cash%20advance-Bugando%20MC'%24J%3A%24J)%2C1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%26nbsp%3Badvance%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ECash%20Advance-Bugando%20MC%20is%20the%20name%20of%20the%20worksheet.%3C%2FP%3E%3CP%3Ewhen%20i%20try%20out%20this%20formula%2C%20it%20works%20through%20cell%20J21.%26nbsp%3B%20after%20that%2C%20the%20credit%20about%20is%20off%20by%20the%20debit%20amount.%26nbsp%3B%20in%20other%20words%2C%20up%20to%20J21%2C%20the%20debit%20amount%20and%20the%20credit%20amount%20are%20correct%20then%20the%20debit%20amount%20is%20more%20than%20the%20newly%20entered%20credit.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20for%20your%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1408183%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-1408209%22%20slang%3D%22en-US%22%3ERe%3A%20sum%20if%20dynamic%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1408209%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F671853%22%20target%3D%22_blank%22%3E%40BethBeth101%3C%2FA%3Epls%20share%20the%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

see attached file.  Col i is either debit or credit.  col j is an amount.  in cell s46 i have this formula

sumif($I$15:I40,credit,Cash_Advance_Amount)

 i have named the range "debit" as q46

Cash_Advance_Amount is another named range.  initially i named range $J$15:$J$21 as Amount.  then i went to the Name Manager and edited this . now the Refers To section has this formula - 

=offset('Cash Advance-Bugando MC'!$J$1,0,0,COUNTA('Cash advance-Bugando MC'$J:$J),1)

 advance

Cash Advance-Bugando MC is the name of the worksheet.

when i try out this formula, it works through cell J21.  after that, the credit about is off by the debit amount.  in other words, up to J21, the debit amount and the credit amount are correct then the debit amount is more than the newly entered credit.

 

thank you for your help

1 Reply
Highlighted

@BethBeth101pls share the sheet.