I have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
Please advise.
Manual
=IF(OR(MONTH(A1009)<MONTH(A1010),A1010=""),SUMPRODUCT((A$4:A$65536>=A4)*(A$4:A$65536<=A1009)*(B$4:B$65536)),"")
Dynamic
=IF(OR(MONTH(A1009)<MONTH(A1010),A1010=""),SUMPRODUCT((A$4:A$65536>=?????)*(A$4:A$65536<=A1009)*(B$4:B$65536)),"")
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
Please advise.
Manual
=IF(OR(MONTH(A1009)<MONTH(A1010),A1010=""),SUMPRODUCT((A$4:A$65536>=A4)*(A$4:A$65536<=A1009)*(B$4:B$65536)),"")
Dynamic
=IF(OR(MONTH(A1009)<MONTH(A1010),A1010=""),SUMPRODUCT((A$4:A$65536>=?????)*(A$4:A$65536<=A1009)*(B$4:B$65536)),"")