i trying rewrite sumifs formula sumproduct can reference closed worksheets. formula checks column dates if date between 2 values, , if sums column. sumifs formula is
=sumifs('workbookname'!$d:$d,'workbookname'!$a:$a ">=7/1/2015",'workbookname'!$a:$a"<=9/30/2015") this works long both workbooks open, cannot sumproduct produce other "-" have tried (where a17 , a18 2 dates above)
=sumproduct(--('workbook'!$a:$a>=a17),('workbook'!$a:$a<=a18) ('workbook'!$c:$c))
it appears syntax not quite correct (either or post editor corrupted formula):
=sumproduct(--(workbook!$a1:$a1000>=a17),--(workbook!$a1:$a1000<=a18),workbook!$c1:$c1000)
i recommend don't use entire column references sumproduct. hence use of 1000 upper row reference.
feel free amend value meet requirements, though should careful not make arbitrarily large, since, each additional cell referenced, calculation required.
and applies whether additional cells technically beyond last-used cells in ranges or not. functions, e.g. countif(s)/sumif(s), can away referencing entire columns no detriment performance, though not functions operate on arrays, such aggregate, sumproduct, , construction requiring cse.
even better, make ranges dynamic, such automatically adjust data expands/contracts, offering optimal calculation efficiency. can show how if like.
regards
Comments
Post a Comment