How to reference variable range?
Your idea is clever. There are other ways, but they are not necessarily
"better". For example, a set of formulas like:
=SUMPRODUCT((B9:B721H1)*(C9:C721="Tag2"))
=SUMPRODUCT((B9:B721=H1)*(B9:B721H2)*(C9:C721="T ag2"))
is another way, but is it "better" ?? Your technique works because your
data is pre-sorted by tag.
--
Gary''s Student - gsnu2007xx
"WhatsUp31415" wrote:
I have one column of data (B9:B721) and a parallel column of tags (C9:C721).
The data are arranged with all of "Tag1" data, followed by all of "Tag2"
data, etc.
I want to be able to reference all of the data for "Tag1", or all of the
data for "Tag2", etc. For example:
=FREQUENCY(tagrange,H1:H15)
Currently, I use INDIRECT to construct tagrange:
INDIRECT("B" & MIN(IF(C9:C721="TAG2",ROW(C9:C721))) &
":B" & MAX(IF(C9:C721="TAG2",ROW(C9:C721))))
Is there a better way?
The ideal variable reference would not require an array formula, as the
MIN/IF usage above does, even though the example, FREQUENCY, must be an
array formula anyway.
|