Need Date Range Formula: Beginning of month to end of month (which is in the current row)

[KevH]

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)),"")

 

[gardnertoo]

Here's an idea:

=IF(MONTH(A1010)>MONTH(A1009),SUM(OFFSET(B1009,0,0,-COUNTIF(A$1:A1009,">="&DATEVALUE(TEXT(MONTH(A1009),"00")&"-01-"&TEXT(YEAR(A1009),"0000"))-0.001),1)),"")

Instead of trying to find the last data point for January (for example) by looking up, I look at the next row and determine if THAT data point comes from the next month. If so, then SUM the range defined by the OFFSET, which does look up to A$1 and finds all the data points with timestamps greater than a few minutes before midnight on the first day of the month.

 

[Peter_SSs]

KevH

Not sure I have understood exactly what your data is like and what you are trying to do, but see if this is it. This formula would go in C2 and get copied down . It assumes your dates are in chronological order.

=IF(MONTH(A2)=MONTH(A3),"",SUMPRODUCT(--(TEXT(A$2:A2,"mmm-yyyy")=TEXT(A2,"mmm-yyyy")),B$2:B2))

You may find that if you have a lot of data, the great number of SUMPRODUCT formulas might slow your sheet down. If so, post back as there may be some alternative ways. A small amount of sample data might also help.

 

[KevH]

KevH
You may find that if you have a lot of data, the great number of SUMPRODUCT formulas might slow your sheet down. If so, post back as there may be some alternative ways. A small amount of sample data might also help.

It's anywhere from a month of data to a year of data (15-16k rows) on this one tab. The plan is to distil it down to monthly sums, min's, max's, & averages so, yes, definitely getting some slow down. Time to switch to VBA?

Instead of trying to find the last data point for January (for example) by looking up, I look at the next row and determine if THAT data point comes from the next month. If so, then SUM the range defined by the OFFSET, which does look up to A$1 and finds all the data points with timestamps greater than a few minutes before midnight on the first day of the month.

So this creates a running sum? For example the February sum would include both the Jan & Feb numbers and the March would include Jan-March?

If not, then I don't follow. Please clarify.

Thanks guys,
Kev.

 

[gardnertoo]

The Feb sum goes back to the first data point after the last January data point, which is the first Feb data point.

 

[KevH]

gardnertoo, your formula only gives the very first month of data - all other months seem to be ignored. I'll try to tweak it and see if it's just pilot error.

Peter_SSs, you formula does the trick, but I'd still like to see what other options you had in mind. To test your formula I sarted deleting rows & comparing to a manual SUM formula. It was able to keep up with my edits without issue.

As an FYI, trying to run the three test columns of SUMPRODUCT plus a SUM column makes me want to alternate between FacePalm & HeadDesk.

 

[Dave Patton]

Try one of
1. Pivot Table
2. Subtotal
3. Sumproduct

In a range of cells such as D3:D14 enter dates for the 1st of each month i.e. 1/1/09
(format to your preference)

Formula to give total for each month (accounting for month and year) is

=SUMPRODUCT(--(INT($A$1:$A$2000)-DAY($A$1:$A$2000)+1=D3),($B$1:$B$2000))

 

[KevH]

In a range of cells such as D3:D14 enter dates for the 1st of each month i.e. 1/1/09

Unfortunately, this aproach fails the dynamic requirement.

1. Pivot Table
2. Subtotal
3. Sumproduct

Which of the three options would work best if:
- I know what my max range is (366 days * 48 samples per day = 17568 rows)
- I <b>don't</b> know the range of the file I'm receiving.

A single SUMPRODUCT column seems to be the consensus.

 

[Dave Patton]

I would vote for Pivot Table, then subtotal, and then SumProduct.

With my suggestion, the results are shown in compact table; not along the data.
Please choose the method that meets your needs or preferences.

re Dynamic

With Excel 2003 look at Data List.

You can also check Dynamic Named Ranges or edit the ranges specified to 20000.

 

[Dave Patton]

You stated "The plan is to distil it down to monthly sums, min's, max's, & averages "


One Pivot Table can present the Sum, Max, Min, and Average by Month.
Excel's Pivot table 
will look much better than the following and you can format it as required.


		Data			
Years	Date	Sum of Amt	Max of Amt2	Min of Amt2	Average of Amt2
2009	Jan	   22		22		22		22
	Mar	1,590		82		24		53
	Apr	  170		86		84		85
Grand Total	1,782