Thanks, Pete!

--
Biff
Microsoft Excel MVP


"Pete_UK" wrote in message
...
Hey Biff,

I notice you've just reached 10,000 posts in the All-time GG archive
for this group - Many Congratulations !!

Pete

On Nov 13, 10:26 pm, "T. Valko" wrote:
I'm trying to do everything with formulas.

So that means you're not using DataFilter?

Not a problem!

To average the rent on bldg's with =100 units and <=299 units:

Array entered** :

=AVERAGE(IF((Sheet2!AD1:AD10=100)*(Sheet2!AD1:AD1 0<=299),Sheet2!BB1:BB10))

Better to use cells to hold the criteria:

A1 = 100
B1 = 299

=AVERAGE(IF((Sheet2!AD1:AD10=A1)*(Sheet2!AD1:AD10 <=B1),Sheet2!BB1:BB10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

"Bryan (aka The
Perfectionist)"<BryanakaThePerfection...@discussio ns.microsoft.com wrote
in message

...



Thanks for the response. I should have mentioned, the summary table is
on
a
different worksheet than the raw data, so I'm trying to do everything
with
formulas.

"T. Valko" wrote:

If you're using DataFilter then you want to use the SUBTOTAL function
to
ge
the average of the visible rows. If you're filtering on column AD then:

=SUBTOTAL(1, BB2:BB100)

1 is the index number for average. See Excel help on SUBTOTAL for other
index numbers and what they mean.

Just make sure you put the formula outside of the filtered rows. It's
usually a good idea to put the formula above the filter.

--
Biff
Microsoft Excel MVP

"Bryan (aka The Perfectionist)" <Bryan (aka The
wrote in message
...
I've been working on this for hours, and I desperately need help.

I'm simplifying the spreadsheet immensely, but the heart of my
problem
is
this: In my spreadsheet of apartment buildings, the # of apartments
in
a
building is in column AD, and rent per apt. unit is in column BB:

AD BB
228 800.00
450 880.00
964 870.00
290 760.00

I've been asked to create a summary table which calculates the
average
rent
per unit for various ranges -- for instance, the average rent per
unit
for
buildings with 100 to 299 apartments, 300 to 499 apartments, etc.

So I need to filter rows in column AD to show just the buildings
within
a
range (100-299 apartments, for example), then sum column BB for only
those
AD-filtered rows, then average column BB for only the AD-filtered
rows.
I've
read hundreds of posts and tried dozens of combinations of SUMIF,
COUNTIF,
and SUMPRODUCT, and I'm just not getting there.

Please save me!!!

A million thanks.- Hide quoted text -

- Show quoted text -