complicated row searching/counting

Hi everyone - I hope you can help me with this.

I am in charge of the turnover to support of 70 some projects, and so I run a report that exports to Excel all projects (past and present) that I have been involved on.

I want to be able to make some simple 'queries' of this data - how many are currently active, how many have I closed, how many have I cancelled, what is my average time between estimated and closing dates, etc.

The columns I will be dealing with are:
Production Date
Turnover Status
Project Status
Estimated Closed Date
Actual Closed Date

The first couple of queries are in theory fairly simple: just count all projects that ProjectStatus column= Closed, for instance, or where TransitionStatus is NOT Closed/Cancelled.

However, when I need to check based on multiple columns, I get lost. For instance, I need to find all projects that are not ProjectStatus = "On Hold", and Estimated Closed Date < TODAY, or the average difference between Estimated and Actual Closed date of all projects where TurnoverStatus = Complete.

I know, they're beginning to sound like SQL statements... but how do I do this sort of complicated calculations in Excel?

Your help is GREATLY appreciated! Thank you, in advance!

-Will

There are many differrent approaches to this. I think the best one for you will be to first add Autofilter. You find this on the Data menu. Then you can filter your list to suite your criterias.

If you also need some calculation on this, the next thing you do is to check out the subtotals functions. You also find this on the datamenu. This gives you choices on summing, counting, averaging etc on your filtered results.

This is fine for doing one time checks on different criterias. If you need permanent results without this visual filtering, there are ways of using SUMPRODUCT function to do conditional summing og values in one column based on criterias in multipel columns. But I don't think this is what you are heading for right now.

You would maybe also like to check out Pivot tables (also on the data menu). This gives you more or less the same results as you get with Autofilter and subtotals, but sorts all possible results of different filter criteria in a table where you can view all at the same time ! You can easily change the layout of this table and change or combine the use of sum, count, average etc.