1

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. :(

  • Can you type in some example data (showing what output you want)? Your second paragraph says, "... between the intersection ...". Between that and what? – Scott Feb 28 '15 at 2:25
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.

0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.