Best to set up defined names for your ranges such as
edit>name>define>name>DEPT>in the refers to box something like
=offset($a$1,1,0,counta($a:$a),1)
the same for paygrade
then to count them
=sumproduct((dept="IT")*(paygrade=6))
then for your average use this array formula entered with ctrl+shift+enter
=average(if(dept="HR"),paygrade))
--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"mvp23" <mvp23@discussions.microsoft.com> wrote in message
news:312E66C6-4CC4-45A3-B36B-622B7C128F5B@microsoft.com...
>I need help devising formulae/VB to do the following:
>
> I have a database list that I copy into Excel as a worksheet. In excel, I
> have many different worksheets that do calculations based on this list.
>
> Example database list:
>
> Dept Pay Grade Count Avg. Wage
> IT 5 2 50
> IT 6 3 75
> HR 4 1 40
> HR 5 5 50
> Sales 8 1 100
>
> How do I:
> 1) From a cell in my summary report, I want it to automatically lookup how
> many people in IT are Pay Grade 6 (i.e. return the count for the row where
> the Dept and Pay Grade criteria are met).
>
> 2) From another cell in my summary report, I want to automatically lookup
> what the average wage is for people in HR who are Pay Grade 4. (i.e.
> return
> the avg wage for the row where the Dept and Pay Grade criteria are met).
>
> Keep in mind my that I frequently need to refresh my database list so I
> don't want to keep changing 50 cell references in each of 12 different
> worksheets every time I reload the list. I need it to automatically look
> this info up from the same range of cells where I always paste updated
> lists.
>
>
>
>
>
> What formula can I use to return a value based on three conditions
>
>