Return a Value Based on 3 Critera

[Guest]

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

 

[Don Guillett]

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))

 

[Bob Phillips]

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).
=SUMPRODUCT(--(A2:A20="IT"),--(B2:B20=6),C2:C20)


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).

=AVERAGE(IF((A2:A20="HR")*(B2:B:B20=4),D220)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

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.

Just set the end of the range greater than you will ever need.