Range reference in array formula

[Ken McLennan]

G'day there One & All,

I've come across an issue that's not amenable to swearing &
cursing and has got me stumped.

I've got a simple data list of office locations in column G, with
the next few columns identifying data such as gender indicated by a "X"
in the appropriate column.

My list is generated from a number of other documents which
change each reporting period (sometimes daily) and so can be of a varying
number of rows.

I need to determine the number of males in say "A-Town" office. I
do so with an array formula "=SUM((G1:G500="A-Town")*)(H1:H500="X"))".

This works fine, however I don't know how many rows there'll be
so that the last row might be greater than "G500". I tried using "G:G" &
"H:H" but that resulted in an error situation. Chasing that indicated
there were too many rows to calculate properly but with a lower number it
worked fine.

What I would is limit my formula to just those rows that hold
data but I don't know how to put that into the formulas. I tried a UDF I
called DataRng() (/imagination overkill) in this fashion "=SUM((DataRng(G)
="A-Town"..." but that didn't work. I've tried calculating the last row
with a UDF in "=SUM(("G1:G" & LastRow(G)="A-Town"..." with the same
result. Nor did using a dynamic range for each column.

Is there a way I can get a calculated address into my
arrayformula? Google hasn't given me anything, but I may be using
inappropriate keywords. If anyone has a pointer then I'll be only too
happy to hear it.

Thanks for helping,
Ken McLennan
Qld, Australia

 

[GS]

This might not be what you're looking for BUT, if you substitute the
use of number 1 instead of placing X in the appropriate column then you
could use =SUMIF($G:$G,"A-Town",H:H).

Note that the lookup range is absolute while the return range is
relative, so copying it to the appropriate column to count females
works there as well.

 

[Ken McLennan]

G'day there Garry,

This might not be what you're looking for BUT, if you substitute the use
of number 1 instead of placing X in the appropriate column then you
could use =SUMIF($G:$G,"A-Town",H:H).

Thanks very much for that mate. I'll have to wait until I get to
work, as I don't have a Windows box here at home but if all goes to plan
then that should do the trick.

Note that the lookup range is absolute while the return range is
relative, so copying it to the appropriate column to count females works
there as well.

Which will make it much simpler to write the other 8 formulae for
the other criteria

Thanks very much again, it's greatly appreciated.

See ya
Ken