Please write the most efficient excel formula that would look at the row numbers listed…
- User
Please write the most efficient excel formula that would look at the row numbers listed in DM3:DM150 of the current sheet and then sum the values from column I of those rows in the "FS Balance" sheet only if the date next to the row numbers in DN3:DN150 of the current sheet is a January date, and if the value in column I of the "FS Balance" sheet is a negative value. Please account for the fact that there may be blank values in columns DN and DM of the current sheet
- Assistant
Sure, you can use the following Excel formula to achieve this efficiently:
=SUMPRODUCT( --(ISNUMBER(DM3:DM150)), --(ISNUMBER(DN3:DN150)), --(MONTH(DN3:DN150)=1), --(INDEX('FS Balance'!I:I, DM3:DM150)<0), INDEX('FS Balance'!I:I, DM3:DM150) )Explanation:
-
ISNUMBER(DM3:DM150): Checks that the entries inDM3:DM150(row numbers) are numbers and not blanks or text. This ensures we only process valid row numbers. -
ISNUMBER(DN3:DN150): Ensures the dates inDN3:DN150are valid numbers (dates) and not blanks or text. -
MONTH(DN3:DN150)=1: Tests if the dates inDN3:DN150are in January. -
INDEX('FS Balance'!I:I, DM3:DM150)<0: Retrieves the values from column I of the "FS Balance" sheet at the rows specified inDM3:DM150and checks if they are negative. -
INDEX('FS Balance'!I:I, DM3:DM150): Retrieves the actual values to sum.
Each condition returns an array of TRUE/FALSE values, which are coerced to 1/0 by the double unary
--operator. TheSUMPRODUCTfunction multiplies these arrays element-wise and sums up the products, effectively summing the desired values that meet all conditions.Note: This formula assumes that when
DM3:DM150contains blanks or invalid references, theINDEXfunction will handle them appropriately. If there's a possibility of errors due to invalid row numbers, you can wrap theINDEXfunction withIFERRORlike so:=SUMPRODUCT( --(ISNUMBER(DM3:DM150)), --(ISNUMBER(DN3:DN150)), --(MONTH(DN3:DN150)=1), --(IFERROR(INDEX('FS Balance'!I:I, DM3:DM150),0)<0), IFERROR(INDEX('FS Balance'!I:I, DM3:DM150),0) )This modification ensures that any errors resulting from invalid row numbers are treated as zero, preventing the formula from failing due to such errors.
Final Formula:
=SUMPRODUCT( --(ISNUMBER(DM3:DM150)), --(ISNUMBER(DN3:DN150)), --(MONTH(DN3:DN150)=1), --(IFERROR(INDEX('FS Balance'!I:I, DM3:DM150),0)<0), IFERROR(INDEX('FS Balance'!I:I, DM3:DM150),0) )This formula efficiently sums the negative values from column I of the "FS Balance" sheet at the specified row numbers, only when the corresponding dates in
DN3:DN150are in January, and accounts for any blank or invalid entries in bothDMandDNcolumns.Answer:
An efficient formula is:
=SUMPRODUCT( --(ISNUMBER(DM3:DM150)), --(ISNUMBER(DN3:DN150)), --(MONTH(DN3:DN150)=1), --(INDEX('FS Balance'!I:I, DM3:DM150)<0), INDEX('FS Balance'!I:I, DM3:DM150) ) -