Sum of subset of an array
 

Hi,

I have a worksheet with monthly sales figures on it. The first column
contains the sales person and the first row contains the sales period.
What I need is a formula that can select a row based on the sales person
name and select a subset of columns based on a period range and produce the
sum of the values.
I'm looking at SUM, SUMIF, INDEX and MATCH but can't quite figure out how to
organise them to provide the desired result.

I've got as far as this, which will return the value of the correct row in
the first column. Is there some way to return a cell reference instead so
that I could slot it into a SUM function?

=INDEX('Sales Data'!$B$4:$AL$162, MATCH($J$5, 'Sales Data'!$B$4:$B$162, 0),
MATCH("P1", 'Sales Data'!$B$4:$AL$142, 0))

Sum of subset of an array
 

"Freeflyer" wrote:

Hi,

I have a worksheet with monthly sales figures on it. The first column
contains the sales person and the first row contains the sales period.
What I need is a formula that can select a row based on the sales person
name and select a subset of columns based on a period range and produce the
sum of the values.
I'm looking at SUM, SUMIF, INDEX and MATCH but can't quite figure out how to
organise them to provide the desired result.

I've got as far as this, which will return the value of the correct row in
the first column. Is there some way to return a cell reference instead so
that I could slot it into a SUM function?

=INDEX('Sales Data'!$B$4:$AL$162, MATCH($J$5, 'Sales Data'!$B$4:$B$162, 0),
MATCH("P1", 'Sales Data'!$B$4:$AL$142, 0))



Ok, with a little more perseverence I got this:

=SUM(OFFSET('Sales Data'!$B$6, MATCH(CONCATENATE($J$5, " UDM Net Sales"),
'Sales Data'!$B$7:$B$162, 0),MATCH(LEFT(K$2, 7), 'Sales Data'!$C$3:$AL$3),1,
'Sales Data'!$B$3))

which is working for me now.