Help on a summing a range of values from one tab to another

[JackSmith123]

Hello excel gurus:

Good day - I am working on an budget template with 2 tabs. One tab holds weekly data and the other tab tabulates the weekly data into monthly data.

I have attached a sample of the file. This sample file of course is very basic but the actual file has thousands of lines of data but essentially the structure is very similar.

Currently, in the 'monthly data' tab I'm using a SUMIFS function to sum a range from the 'weekly data' tab. I believe however, that a superior function or combined function could be used instead which will tabulate the data from the 'weekly tab'. I'm thinking either SUMPRODUCT or an INDEX/MATCH or maybe a VLOOKUP combined with a SUMIFS. I prefer not to use SUMIFS because the weekly data is not so neatly listed and there are blank spaces in between lines. With the SUMIFS i have to keep on changing the range which is quite cumbersome.

The function has to look at 2 criteria: Lookup a code in column A of the 'monthly tab' and then sum by month all of the values relating to this specific code which exists in the 'weekly data' tab.

For example, in cell B4 of the 'monthly tab' I would like the formula to look up '4000' (Cell A4 in the monthly tab) in the 'weekly data' tab and sum the values in the range B3:F3 from the 'weekly data' tab. I want to make clear that I do need the function to lookup a value from a row and a column however the the function is not returning one value but a sum of range of values.

Any help would be much appreciated!!

[AliGW]

I don't quite see the issue. Please, would you repost the attachment, this time with the monthly tab manually mocked up to show what you really want.

[protonLeah]

Try:

Please Login or Register  to view this content.

[shukla.ankur281190]

Or Try

B4

Please Login or Register  to view this content.

Drag towards and down.!!

[FlameRetired]

Try

Please Login or Register  to view this content.

[JackSmith123]

Thanks everyone for providing a solution. I think all solutions work great but I really like the Sumproduct one. Thanks for taking the time to offer a solution - you guys are lifesavers!

One other question for you: The Weekly data tab is constantly changing and there are times when new rows are inserted as there are new GL codes being added constantly. Currently the Weekly data tab has approximately 1000 rows and the Monthly tab has 5000 rows in my actual budget template (I had only shared the sample template which was fake). I don't think the workbook will grow substantially from what it is now, but just wondering if you guys recommend that I name ranges using the name manager feature in excel or some other method? I'm just wondering if there is an easier way to do this as I have to keep on adding/inserting rows in both the weekly data and then consequently update the monthly tab to reflect what is in the weekly tab. My concern is that if the worksheets grow in the number of rows then I also have to update the references/ranges in name manager - this is particularly challenging as I have A LOT of weekly tabs not one. I realize this may be outside the scope of this particular thread. Any recommendations would be greatly appreciated!!

[FlameRetired]

It's completely in the scope of this thread.

It depends on which approach you use. You state a preference for SUMPRODUCT. In that case I would recommend Dynamic Named Ranges. Perhaps even consider Tables ... if the formula syntax isn't too much to contend with. They both "grow" with additional data. No need to edit formulas exists. Just be sure to steer clear of referencing whole columns/rows with SUMPRODUCT. That taxes the system unnecessarily.

If you are willing to reconsider the SUMIF approach you can reference whole columns/rows with little or no detriment to performance. That is another way to "future proof" formulas. In the example I posted #5 I attempted to demonstrate that referencing rows 3:1000. You could make that 3:10000 or larger ... beyond the limit of what you anticipate ... and it would still work.

Did this help?

[JackSmith123]

Many thanks for your feedback FlameRetired. I think I will reconsider using the SUMIFS approach as that way I don't have to keep on changing the ranges. Awesome!! thanks again for your help.

[FlameRetired]

You're welcome. Thanks for the feedback.