Forum Discussion
Changing an array formula for a different cell range
Thank you for your help. I am attaching an example. I need the journal entries to autofill based on the date of the journal entry, with the sum of the date of the entry and prior 6 months. The data is in the Summary tab, but I have also transposed it (Transpose tab) - the range can come from either tab. However, each line on the JE refers to a different range, which I have color coded the first few as an example. !st attachment is JE I need formula, second is part of the summary sheet (horizontal) and 3rd is the Summary sheet Transposed. Any help is appreciated!
Kerry_83 I don't have the sheet to test on but I think a better approach would be:
=SUMIFS(Transpose!$S$1:$S$1000, Transpose!$A$1:$A$1000,"<="&EOMONTH(A4,0), Transpose!$A$1:$A$1000,">="&EOMONTH(A4,-6), Transpose!$B$1:$B$1000,"Interest")
That said I have a couple of notes:
this assumes column A dates are actually dates and not TEXT that just looks like a date
that you actually do want last 6 months of values (including all of the month for the given date as I did here or should it only be <= that exact date). Part of this question is because I noticed a couple of the months had an ADDED entry. is that why the previous included 7 entries instead of 6? Were you forced into 6month in order to guarantee a consistent number? With this formula you can choose the time period.
To change from Interest to Cash Int or Accrued Int just change the "Interest" accordingly.