How to count based on several checks. 1

[Hasit]

Heres one I have been wrestling with for a few hours. I am using Excel 2000.

I have 4 columns. They are:

Column A: Unique (building) key
Column B: Square footage per building
Column C: A value of either "bid", "contract" or "core"
Column D: A value of either "occupied", "part occupied", "surrendered" or "vacant"

I need to do two things using formulas only (no VBA, macros or otherwise for various reasons I will not bore you with):

First, I need to check for properties that are occupied or part occupied and they are bid only (I have to check for the other values too, but if I can do one, I can do the others!) and then count the number of these.

Second, for those buildings that meet specific criteria (for example, "bid", "occupied" or "part occupied&quot, I want to add the square footage and divide by the number that make up the total (to get an average).

For the second requirement above, I have used conditional sum and it works a treat, but I would like to achieve what I need (for both requirements) using a single formula, so I do not have to go through 2 or more steps to get the result.

I have an additional requirement, which I will put in another thread so as not to confuse matters (I will x-ref it to this one and vice versa).

Any thoughts??

 

[Hasit]

See this thread for related info: thread68-184939

 

[Hasit]

Dale,

I have sent you a sample file.

Thanks!
Hasit.

 

[Hasit]

All. Dale has come up trumps again. He has been able to resolve my problem using a combination of DCOUNT and setting up named tables.

If anyone wants the worked example (I hope Dale does not mind), I can send you a copy.

hasitbakhda@yahoo.co.uk

[Strange thread this. The problem has been solved by someone else and the only names appearing in the thread is mine! Dale: log on so I can give you a star!]

 

[DaleWatson123321]

Update to this thread ...so other Tek-Tippers will know that such a problem can be resolved. And if you'd like a copy of the solution, I can email you a small example file.

Hasit has just confirmed that the proposed solution I sent him worked. Here's his words...

"Works a treat - thank you. Strange thing though. Your criteria setup is exactly what I did, but it would not work. I used the DCOUNT function as well, so I have no idea what the difference is between my version and yours
(except your works beautifully!)"

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
or nd.watson@shaw.ca