Conditional Summing across ranges using arrays

M

Marston Gould

Hi all -

I'm struggling to find a formula that will solve my problem:

I have a matrix of numbers defined by a series of numerical column and
row headers.

RefCel 1 2 3 4 5 . . . m

1 a # # # #
2 # # # # #
..
5 b # # # #
..
n # # # # #


What I'm trying to do is for a give column header and a subset of row
headers,
sum the intersecting values. Let's assume that the subset are held in
a named range Subset and the row and column headers are in range
called RowHead and ColHead and that the column I'm interested in is
ThisCol

I tried something like this
={sum(offset(RefCel,match(Subset,Rowhead,0),match(ThisCol,ColHead,0),1,1))}

So if ThisCol = 1, RowHead started at 1 and went to n, ColHead went
from 1 to m,
and Subset included a range with two values, 1 and 5, what I'm trying
to get to is a+b. Unfortunately, all I'm getting is a.

A complicating factor is that some of the values in Subset may not be
found in Rowhead.

Any thoughts on how I get this one done?

Thanks,

Marston
 
Top