Excel '10 Formula to find & sum a row based on the intersection of 2 data points. (ie. Name(row), Month(column)

Question

I need an Excel 2010 array formula that will take 2 given data points, say a "Name" (row) and "Month" (column), and return the sum of a row based upon the intersection of these two points within a data table. (I apologize, but I'm not able to insert a JPG.) For example, my table's "Name" reference range is A2:A15, and the corresponding "Month" range is B1:M1. The data contained in the table itself would be monthly sales by name.

The required formula would return the "YTD" sum of all values (monthly sales) contained in a row between the intersection of a specified "Name" & "Month," effectively returning the YTD value for a given "Name" based on a specified "Month."

For my formula (located outside of the table), my "Name" data point reference is cell "B20" and my "Month" data point reference is cell "C20." (I hope this makes sense!!)

I have experimented with MANY variations of SUM(INDEX(MATCH and SUM(OFFSET(MATCH formulas, but to no avail. The difficulty is having to solve for two data point references. :(

Answer 1

You could use the following formula:

SUM(IF(A2:A5=B20,OFFSET(B2,0,0,4,MATCH(C20,B1:M1,0))))

And then press Ctrl+Shift+Enter to tell Excel it is an array formula.

Answer 2

What does B1:M1 contain exactly? Text values like "Jan", "Feb" etc. or dates?

If they are dates you could use SUMPRODUCT like this

=SUMPRODUCT((A2:A15=B20)*(B1:M1<=C20),B2:M15)

or for text values try

=SUMPRODUCT((A2:A15=B20)*((1&B1:M1)+0<=(1&C20)+0),B2:M15)

Answer 3

I'd prefer using MATCH() to feed OFFSET() the row and column for the endpoint, and row for the start ('cause you know it's January by definition so you can just use a 1 instead of the function):

=SUM(OFFSET(A1,MATCH(B20,A2:A15,0),1):OFFSET(A1,MATCH(B20,A2:A15,0),MATCH(C20,B1:E1,0)))

and let SUM() add the range up.

Why use MATCH()? Not to mention it's a good habit for remembering you can remove the impact of row or column insertion/deletion by doing so, but also, it lets you use the natural values available for the lookups: the row and column headers.

You could "improve" it if needed by replacing the "1" I mentioned making it always start with January with a MATCH() formula asking for the start month as a third parameter to go with B20 and C20. Then you could leave it set with "Jan" usually, but change it any time you wished to have a period like Apr-to-Oct reported.

Of course, adjust how the Date header is approached if you have anything other than text in that header.

Nowadays you need not use {CSE} entry, but would have in 2010... as I believe SUM() is NOT one of the functions that is able to evaluate an array without {CSE}. Not dead sure though. In any case, there is no possible downside to using {CSE} as the result is in a single cell so no obnoxiousness arises.

So, simple to understand and maintain over time. Gives the right answer. What more does a simple Excel builder want?