# Sum Data in Two Columns For Last Month Based on Date Cell Reference

#### twothings

##### Board Regular
Greetings all

I am familiar with the SUMPRODUCT function however when introducing multiple sheets and a date problem into the scenario I am getting no success

What I have is:

Sheet 1
B8:B373 - days of the year
D8:D373 - number data
E8:E373 - number data

Sheet 2
B8:B373 - days of the year
D8:D373 - number data
E8:E373 - number data

Sheet 3
H102 - date entered by user

I would like two formulas, one that can sum all data from both sheets for dates in the current month and also for dates in the previous month based on the date in cell H102. These will be placed on Sheet 3.

This was working for me to determine the previous month but not sure if this was on the right path:
Code:
``=TEXT(DATE(YEAR(\$H\$102),IF(MONTH(\$H\$102)-1>0,MONTH(\$H\$102)-1,12),1),"MMMM")``

This got me nothing but errors:
Code:
``=SUM(IF(MONTH('Sheet 1'!\$B\$8:\$B\$373)=TEXT(DATE(YEAR(\$H\$102),IF(MONTH(\$H\$102)-1>0,MONTH(\$H\$102)-1,12),1),"MMMM",'Sheet 1'!\$D\$8:\$E\$373)))+SUM(IF(MONTH('Sheet 2'!\$B\$8:\$B\$373)=TEXT(DATE(YEAR(\$H\$102),IF(MONTH(\$H\$102)-1>0,MONTH(\$H\$102)-1,12),1),"MMMM",'Sheet 2'!\$D\$8:\$E\$373)))``

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
I would solve this this way:

I add all data on 1 worksheet (e.g. with an macro, or manualy copy and paste).

After that I use a pivot table to analyze the data.

Thanks Oeldere, I appreciate your feedback but I only need two totals and to be copying all data and creating pivot tables it is a little too much for the worksheet i've got and prefer two cell formulas for it.

Hi there, still working on this. Wondering if anyone can help with this one still Appreciate any assistance! Thank you

Besides a pivot table, does anyone have any suggestions as an alternative?

What I have is:

Sheet 1
B8:B373 - days of the year
D8:D373 - number data
E8:E373 - number data

Sheet 2
B8:B373 - days of the year
D8:D373 - number data
E8:E373 - number data

Sheet 3
H102 - date entered by user

Hi.

Which precise columns are you summing? Are you wishing to return a single total value from four columns (D and E from both sheets)?

And is this for a given day? Or all days within a given month? What exactly is entered in cell H102?

Regards

Hi XOR LX

I am hoping to achieve two totals. 1. The sum of columns D8:D373 on both sheets, 2. The sum of columns E8:E373 on both sheets. The basis for the total is a variable based on what the current month is.

For example, if the sheet was active today 23/09/14, it would have a result of a total for the last month August (automatically changing to give the total of the previous month based on what today() is . The other result is for the current month, which would be data upto and including 23/09/14.

I hope this makes sense. cheers

Since you've only got two sheets there's no real value in setting up some 3D solution.

You'd need something like:

=SUMPRODUCT(0+(MONTH(Sheet1!B8:B373)=MONTH(Sheet3!H102)),Sheet1!D8:D373)+SUMPRODUCT(0+(MONTH(Sheet2!B8:B373)=MONTH(Sheet3!H102)),Sheet2!D8:D373)

which would give the combined total across column D in Sheet1 and Sheet2 where the respective entries in column B of those sheets has the same month as the value in Sheet3 H102.

I trust you can adapt this to get the other counts?

Regards

Replies
4
Views
435
Replies
2
Views
539
Replies
14
Views
1K
Replies
8
Views
285
Replies
0
Views
462

1,196,154
Messages
6,013,756
Members
441,781
Latest member
Gian Carlos

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