[SOLVED] Sort data using two criteria?

[jruiz81]

Hello,
I'm pulling my hair on something that seems so simple.

Here is the problem:
I have multiple employees working on different projects. I would like to use a formula or some kind of macro where I can automatically scan an entire worksheet and manipulate the data to show me how much time was spent by each employee on each project. Using the SUMIF command only lets me use one criteria. In my case I need to sum if in the row there is a match with the employee name and the project name.

For example, if Employee A is working on Project 1 then sum the units.

I would like everything summarized in a nice table.

Attached is a basic example of my sheet.

Any help is appreciated!!

Thanks!

by the way, I am using a Excel 2000.

[solnajeff]

Hi

I have attached an example using SUMPRODUCT for you to look at.

Regards

Jeff

[Richard Buttrey]

Hi, See the attached.

The C3 cell on Sheet1 will give you the filtered total whenever you filter the data.

The table on Sheet2 is a summary table.

With your production data I suggest you create dynamic range names for the various 8:51 column ranges so that you don't have to worry about changing the formulae. The Sheet2 formulae are array formulae and very intensive on calc time so you need to keep the ranges covered to a minimum.

Rgds

[jruiz81]

This is perfect! So simple!

Thanks a bunch!!