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

[twothings]

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:

=TEXT(DATE(YEAR($H$102),IF(MONTH($H$102)-1>0,MONTH($H$102)-1,12),1),"MMMM")

This got me nothing but errors:

=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)))

 

[Oeldere]

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.

 

[twothings]

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.

 

[twothings]

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

 

[twothings]

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

 

[XOR LX]

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

 

[twothings]

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

 

[XOR LX]

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