Sum 7 days of information based on week ending date

The set up of the sheets of the 2 (houses) is the same.

You mind using VBA to consolidate the data?

You're familiar with pivot tables?

If so I could make an solution for you.

Hi

You can Summarize the data very easily with a Pivot Table.
I created Tables from your data on sheets House 1 and House 2, which I called t_house1 and t_house2 respectively
For convenience, I added an extra field called Year to each table with the formula =YEAR([@Date])

Then each table was summarised with a Pivot Table, with a Grouping on Date into 7 day periods. Year was taken to the Report Filter area, so that you can limit your view to a single year or any aggregate of years.

If you wanted, you could copy all the data from each table to a new table, with the insertion of a field for House number, and then base the PT on the new combined data.
Unfortunately, you list yourself as having XL2007, so you won't have Power Pivot available.
If you had XL2010 or later, then with Power Pivot you could take data from each table into a combined Pivot Table.

Please note, I saved the file in .xlsb format in order to reduce the file size below the limit for uploading to this formum.

I used the macro below to consolidate the data on 1 sheet (consolidated).

After that I add the UDF WeeknumberISO (in the table).

After that I made an pivot table of it.

See the attached file.

Your file was to big to post, so I deleted the summuray sheet.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

an average person can still look at it and understand what the numbers mean (using an sumif formula)?

But using sumif you also have an differant sheet (or range) where you have to put your formula.

I think it's not an better solution.

Why not trying to learn the other people to work with an pivot table.

It is an extreme powerful tool, which MS expands on every version of excel.

My suggestion would therefor be learn and use pivottables.

It also reduces the changes on faillures (in the formula).

Then split the job.

Other people do the input.

You summurize the data an makes the (pivot table) reports (and graphs).

Hi

I don't see what the users have to learn about Pivot Tables?
If you have set up the PT, then all they do is input exactly as present.
providing you can accept some VBA inclusion, then the PT can be made to automatically refresh when the sheet is activated.


On sheet New Summary insert

Please Login or Register  to view this content.

Alternatively, you could hide my New Summary sheet altogether, and consolidtae the PT's into your Summary sheet by inserting the formula
=GETPIVOTDATA(""&C$2,'New Summary'!$A$5,"Week Ending",$B3)+GETPIVOTDATA(""&C$2,'New Summary'!$J$5,"Week Ending",$B3)
into cell C3 and copying across and down

You just need to ensure that the headings in C2:G2 of your Summary sheet are identical to those used in cells B5:F5 of sheet New Summary

In which case insert the following event code on Sheet Summary

Please Login or Register  to view this content.

I see little point in writing formulae with SUMIF, SUMPRODUCT or anything else, when you can use the PT to do all the "heavy lifting" for you, and then simply use GetPivotData to pull the results across to your Summary.

The GetPivotData formula can be simplified to
=GETPIVOTDATA(""&C$2,House1,$B$2,$B3)+GETPIVOTDATA(""&C$2,House2,$B$2,$B3)

if you Name House1 = $A$5 and House2 = $J$5 on sheet New Summary
Then if you had more Houses and more PT's, it is easy to extend the formula by adding everything from the "+" onward, and just changing the House number.

@Roger Govier

Reading your solution , I think this could work for you and me.

But if even an sumif or sumproduct formula is to difficult for the people who have to work with the file, your solution also will not be suitable for OP needs.

@tayanamarie

And what if the formula is deleted or changed by the people?

If they have that less experiance with excel that would be an problem too.



You can only solve this problem by learning people (how to work with excel).


Other solution would be making an button on the sheet, which will run the macro to consolidate the sheets.

A pivot table is just drag and drop (it's very easy to learn).