• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum Horizontally Between Dates Between Multiple Tables

M

MyCon

Member
Hi,

I'm working to make a compiled or cumulative timesheet / PO tracker in which I've been allotted hours & budget & want to make sure I don't go over or be forewarned when I am nearing limits.

I'm looking to track these hours over multiple tables weekly & monthly between the specified PO dates or duration given (not specified through example table).

As you see through example table, I have several tables as follows:

1) Cumulative Timesheet / PO Tracker
-- Primary table to consolidate cumulative totals from Month-to-Month charged hours per project

Others - Month-to-Month charged hours per project

From Table 1 - Cell J10, I have the following that copied across & down for cumulative hours
=SUMPRODUCT(($J$31:$AN$87>=J$7)*($J$31:$AN$87<=J$8),$J34:$AN90)

Formula works well seems odd on how the back end of the formula changes from - Cell AC22:
=SUMPRODUCT(($J$31:$AN$87>=AC$7)*($J$31:$AN$87<=AC$8),$J40:$AN96)

...$J34:$AN90 to ...$J40:$AN96

Not critical to change but would prefer a more consistent function throughout the consolidated table.

=================
Main purpose of post & my questions:

In cells J27 through AC27, looking to consolidate weekly hours from the multiple monthly tables.

How can this be done?

Thanks
 

Attachments

I wouldn't setup my data in Months like you have

You would be better to setup one very wide area and have the dates just keep going from Left to Right, eg: Where you have October, just keep going to the right with November, Dec etc

Then extracting the data between dates becomes very easy

You can still have a monthly report as a report, but not as a data store
 
Hi Hui,

Thanks for replying…

Part of this template is given by employer & the other intent is to keep template printable. For the most part, the format works well for us.

However, as you see, compiling the hours for each month or multiple tables seems to be a bit complex because I can’t have a “running” formula, as if the table was running continually & vertically down the sheet.

Through, “=SUMPRODUCT(($J$31:$AN$87>=J$7)*($J$31:$AN$87<=J$8),$J34:$AN90)”, works well for 2 tables but now looking to expand or use another variation.

Do you have some prospective suggestions?

Thanks for the assistance.
 
Trying to integrate reporting and data is one of the key traps that new players make in Excel and as in your case it leads to inefficient reporting

I would actually have a data area vertically instead of horizontally, with dates extending down the Rows instead of in Columns

Then two report areas (Monthly and Yearly)

Reporting is then very easy in both cases

If I have a minute I will change it and post it back here t show you the benefits
 
Hi Hui,

I absolutely agree & typically will use a vertical format or continual log, which definitely makes extractions a hell of a lot easier than trying to extract from multiple tables.

Looks like I'll need to have a hidden or side table to get needed calculations. Just seems like a lot of double work for the same purpose.

I was hoping there would be a solution here to Sum Horizontally Between Dates Between Multiple Tables but it doesn't appear to be one.

Thanks
 
Hi Hui,

The ">=" & "<=" are correct because I'm extracting totals between desired dates, listed in the date rows or has Monday Start & Sunday End dates.
 
Hi Hui,

Thanks for your example. In terms of having a hidden or off to the side vertical table, we have the same intents.

Your sample formula is a simplified formula than what I would've used but still gets the same results.

I was hoping to avoid the, hidden or off to the side vertical table or having to create a long horizontal table but it appears that there is no way around it.

Thanks again for the assistance.
 
It is so much simpler to use a single table to do reporting from and it allows simpler formulas and flexibility in reporting now and in the future
 
You must log in or register to reply here.
Back
Top