Excel Magic Trick 1071: Dynamic Range Based on Product and Start & End Month: OFFSET, INDEX or AF?

See how to add the sales based on a 3 parameters: Product Name, Start Month and End Month. Video compares and contrasts OFFSET and INDEX function for creating Dynamic Ranges, and also looks at a number of other array formula solutions:
1. (01:15 min) OFFSET & MATCH & SUM functions.
2. (05:16 min) INDEX and MATCH & SUM functions.
3. (08:52 min) What to do if column numbers for lookup are in a single cell like: 2,3,4: OFFSET, MATCH, LEFT, RIGHT and LOOKUP function solution.
4. (14:18 min) SUMPRODUCT & Array constant in VLOOKUP.
5. (15:56 min) SUM, VLOOKUP and a Dynamic Variable-Length Array of Sequential Numbers created with ROW, INDIRECT and MATCH functions.
6. (18:33 min) INDEX to avoid Ctrl + Shift + Enter with formula in #5.
7. (19:05 min) If parameters for all months are given in cells, then a formula with SUMPRODUCT, SUMIFS, INDEX and MATCH can work.
8. (20:22 min) If parameters for all months are given in cells, then a formula with SUMPRODUCT, N, INDIRECT, ADDRESS, MATCH and MATCH with a function argument array operation can work.

Excel Lookup last number in a text string.

[excelisfun]

Excel Magic Trick 1071: Dynamic Range Based on Product and Start & End Month: OFFSET, INDEX or AF? 
This is an EPIC VIDEO filled with many amazing formula tricks, many unusual array formula

[AnalyticsInDetails]

I enjoyed watching your videos. All videos are informative and entertaining as you create different sounds while explaining. This way you are unique and original. We know worth of original is more than copy. You are a great magician.

[love]

Thank you very much, your video helps me a lot. Thanks for sharing your wisdom.

[masterofblight]

This is the coolest video of yours I have seen.

[erendiz]

Hi Girvin, I would like to give you a formula to make the conditional formatting of the months and the SUM function when using OFFSET more robust. This way it doesn't matter in which order the date is chosen, so the From date should not be necessarily <= To date. So when From is > To it also works fine.

Replace your conditional formatting with this one:

and the Sum function on B4 with:

[vspokkuluri]

Hi Girvin, I have made a event management tool for my daily work. A worksheet is designed as year planner with 2 rows for each month. One contains the date and the other row just has Concatenate function to draw values from other worksheet. The other worksheet has my client names, location (place), audit start date, audit finish date and another column for status if the event has been still on schedule or has been cancelled, differed, rescheduled, etc. Through CONCATENATE, INDEX, and MATCH I could draw the Year planner to some extent. I am struggling how to match a date in Year planner to the client's audit start date and finish date, so as to block the dates on the Year Planner to say with which client I am going to be with and how many days the audit will be. Could you please help?

[qezxgoc]

Could it not sum this array but move like it did with highlighted cells. 
I have schedule of occupancy rates (20-20-20-40-40-40-60-60-60) and i want to move it depending on start date. Also it will be perfect if it could fill 90% up to end date).

[wyn]

why do I need to input the N function for the last method? does Indirect function always create a text output?

[cxttrsr]

Hi Mike,
I have a question on the part of creating dynamic range for vlookup in 16:19 to 17:00.
Why should use INDIRECT function to help ROW function to understand the 3:5 with text format?
BTW, can I perceive A:C as column1-3?
It's hard for me to understand this with my limited
Thanks for your help!

[XLarium]

Hi Mike
I tried named ranges (with intersection). Since column headers can go as far as XFD will not work. So I used the full month names and put underscores in the product names.
This will work:
=SUM(February:April Product_4)
But this won't work:
=SUM(INDIRECT(B2&":"&B3&" "&B1))
It only sums the values for February.

[fahmylina]

What to do if one likes to sum-up the contents of "successive blocks of cells" stored in one column (with each block containing 24 cells of a huge data set that has 61368 rows representing hourly solar radiation data during 7 years, form 2004 to 2010)?

I used all what I know and all what you describe in many of your videos to make the said "dynamic ranges" but without any success (since each time there is some error alarm that appears somewhere when using complex formula that contains several telescopic Excel functions). The idea is that nobody is able to go through a huge data set to get the sum of each bloc of such successive cell-blocks by hand.

Excel deceives me as it does not propose any simple way to attack such logic block summation problem down the 61368 rows and we are lost when we use complex formulas that contain many Excel function.

[MrSarky]

Hey Mike,

How do you like this one ?

=SUM(OFFSET(INDIRECT(ADDRESS(14, MATCH(B2, $A$14:$M$14, 0), 4)&":"&ADDRESS(14, MATCH(B3, A14:M14, 0), 4)), MATCH(B1, A15:A19, 0), ))

[KLiCuk]

how does the yellow highlighting work?

[starguy]

Mr Girvin - you deserve a temple. # Excel God

[samkab]

How to sum for several years : January to march and May to June august October to December and so on in one formula