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