How do I do this in Excel?

December 13, 2012 7:26 PM

I am new to working with dynamic arrays in Excel. Also, I would like recommendations on books/resources for advanced Excel users. Help!

I have data in the following format: Google Docs Example

In real life, I will have an arbitrary number of instances of Expense Recoveries and an arbitrary number of Products. I want to create a worksheet that can sum all Expense Recoveries associated with a particular Product.

At work I was trying to do this using match() nested inside index(). The tricky part is that it looks like I need to use multiple dynamically generated arrays to feed into index. Array_2 needs to begin a line below where Array_1 starts. This is where I'm running against the edge of my Excel knowledge.

Lastly, I'm hoping that someone can recommend a book or resource that deals with more advanced Excel issues like this.

posted by prunes to Computers & Internet (4 answers total)

This is a useful discussion of dynamic ranges.
posted by dfriedman at 7:30 PM on December 13, 2012

Here's another relevant link.
posted by dfriedman at 7:33 PM on December 13, 2012

I think you want INDIRECT().

If I'm reading your question right, you want a SUMIF based on product, but only within the (moving) range below the first expense recoveries item. Please correct me if I'm wrong.

If so, and presuming there are some values in column B on your example, then:


=SUMIF(INDIRECT("A"&MATCH("Expense Recoveries - Taxes",A:A,0)&":A999"),"Product 1",INDIRECT("B"&MATCH("Expense Recoveries - Taxes",A:A,0)&":B999"))


will work.

(NB: assumes max of 999 rows for no good reason, and assumes the order of the recovery categories is stable).
posted by pompomtom at 8:08 PM on December 13, 2012 [1 favorite]

Contextures has good stuff, but my go-to for more advanced Excel is Chandoo. Tons, tons of tips and tricks, great forums, and some online courses.
posted by Gorgik at 3:42 AM on December 14, 2012