Dynamic range in SUMIFS

Hi Guys,

I'm new to this forum, but hopefully I'm posting things in the correct format. I'm having an issue with inserting a dynamic range into the SUMIFS function.

\1

What I have is basically an exported Excel sheet of data from my office's system, which I need to process. I just want to see every collector's total collection for each week.

This is the how I'm trying to get the final report to look like:

\1

The SUMIFS function works, but the range has been manually set by my. The number of rows in the data sheet will be different every month, so what I need to do is to have the range dynamic, but I've been unable to do so.

I'm using the following function to find the row (but without the alphabet) after the end of a collector:
=MATCH("total for aliza :",$A:$A,0)

and the following function to find the row before the start of the collector's data:
=MATCH("aliza :",$A:$A,0)

I've been experimenting with INDEX, OFFSET, and a few other functions, but I haven't been able to get it right, with the OFFSET function being something that I'm struggling with.

Any help on this would be greatly appreciated.

The data I'm trying to process has been highlighted in green. I've also attached the Excel sheet here, in case it helps.

Thanks!

Originally Posted by MarkinTX

Have you thought about using a pivot table?
With a few seconds of cleaning up the original data you can use a pivot table to summarize this.

I have uploaded a sample.

Hi MarkinTX,

Yes, I did think about using a Pivot Table, but I wanted a more customized view of my final report (ignore the colours), would that be able to be done with a Pivot Table? I always thought they just look like a basic matrix (but with very powerful results)

\1

Of course, if there is a method to use pivot tables to solve this (especially without having to do a lot of manual adjustments, I'm very interested to find out.

The results in your Pivot Table are what I want.

I'll look into this in detail later and report back, perhaps I should have tried this method.

The thing also is, and I'm not sure if what I'm trying to do is the proper way to process data, I'll need to do this report at least once a month, so I'll have report.xls. I was thinking to have my formulas in offset-text.xlsx (or whatever I rename it to in the future), and basically copy the contents of the whole sheet in report.xls and paste it into a sheet into offset-text.xlsx, and have it automatically populate the report. It probably wouldn't be too productive to clean up the data every month?

Thanks!
Matt

Originally Posted by p24leclerc

Here is a formula that should help you:

Please Login or Register  to view this content.

It adjust the range to the proper rows for the name in column A in sheet Report. the formula also uses the week date in sheet Report.

p24leclerc,

Thanks! This seems to be the function I'm looking for. I'll give it a test on my full data set later today after I've digested the whole thing.

Thanks a lot again! Both solutions are interesting and extremely useful. I'll be back with an update!

Matt

Originally Posted by p24leclerc

Here is a formula that should help you:

Please Login or Register  to view this content.

It adjust the range to the proper rows for the name in column A in sheet Report. the formula also uses the week date in sheet Report.

Hi, sorry for the late reply.

Your function worked beautifully, and the "$A2" allowed me to drag the formula downwards through all the rows, which is awesome.

I did make a little tweak to it, though. As your function showed the cumulative sum, so week 3 would show (week 1 + 2 + 3). I only wanted week 3 itself, but it's my fault because I didn't say that clearly enough.

My modified version of your formula adds a second criteria:

Please Login or Register  to view this content.

Thanks a lot! I've been trying to solve this for weeks without managing to piece together a solution with Google results. One of my main mistakes was not figuring out how to properly concatenate the results together using the "" and &.

I'll also check out if I can use the Pivot Table solution as well, but this works for me at the moment.

Thanks a million again!

Cheers,
Matt