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

#### KevH

##### Board Regular
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.

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

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### gardnertoo

##### Well-known Member
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

##### MrExcel MVP, Moderator
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

##### Board Regular
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

##### Well-known Member
The Feb sum goes back to the first data point after the last January data point, which is the first Feb data point.

#### KevH

##### Board Regular
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

##### Well-known Member
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

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

##### Board Regular
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

##### Well-known Member
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.

re Dynamic

With Excel 2003 look at Data List.

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

Last edited:

#### Dave Patton

##### Well-known Member
Code:
``````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``````

Replies
0
Views
335
Replies
0
Views
200
Replies
5
Views
2K
Replies
8
Views
285
Replies
3
Views
530

1,191,707
Messages
5,988,229
Members
440,139
Latest member
ngaicuong2017

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back