Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return a Value Based on 3 Critera
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return a Value Based on 3 Critera
"mvp23" wrote in message ... 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),D2:D20) 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add cells based on critera | Excel Discussion (Misc queries) | |||
Two Critera to match another spreadsheet and return single criteri | Excel Worksheet Functions | |||
VLookup a given critera and return a value without duplication | Excel Discussion (Misc queries) | |||
Moving data Based on critera. | Excel Programming | |||
Extract data based on critera from three columns | Excel Discussion (Misc queries) |