Queries Within a Spreadsheet

[Mark T.]

How do I perform a query using multiple criteria on one
area of a spreadsheet and return a result set of multiple
rows in another area of the same spreadsheet? Ideally
this would work like a formula and automatically update
the result set based on changes in the data and
a "recalculate". Example:

Data Area
Employee Manager Hours
Jim Suzie 160
Mary Fred 153
John Suzie 162
Sam Suzie 149

Desired Query: Return Employee and Hours where Manager
= "Suzie" and Hours >= 160.

Querie Result Area
Employee Hours
Jim 160
John 162

I don't see that Pivot Tables do what I need. Is there
any other way to do what I'm looking for?

Thanks for any assistance!

 

[Frank Kabel]

Hi
try in column C (cell c1) of your result area the following formula
(assumption: data area is a separate sheet):
=SUMPRODUCT(('data area'!$A$1:$A$100=A1)*('data
area'!$C$1:$C$100>=160),'data area'!$C$1:$C$100)
and copy down