Discussion Locked

Excel Formula Help Needed

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Here goes ... Column A contains names of company locations. Column B contains the quantity of personnel at each location. I'd like to apply a formula to other cells that will categorize the location by the quantity of personnel. For example, "TypeA" offices have 1 - 10 employees; "Type B" have 11 - 20; "Type C" have 21 - 30 ... and so on. My end result should be a count of the total number of each type of office based upon the range of employees.
Example:
Type A = 3 offices
Type B = 10offices
Type C = 0 offices
Type D = 17 offices

Thanks!

5 replies

by zalog

You can solve this using a lookup table. Setp 1:Create a lookup table somewhere on the worksheet (it can be on a different page). The table will have three colums. The first column will contain numbers for the minimum people for the type of office. The second column will contain the office type. The third will be office counts (leave this blank for now, it will be computed in the step 3).
Step 2: Now in a free cell on the row with the Company location and quantity of personnel use thevlookup function. =vlookup(Br,table,2) (where r is the row number, table is the absolute range of the lookup table you created in step 1). This will return the office type in column two of the lookup table. Copy this formula down for each office location.
Step 3. Finally in the third column of the lookup table add the formula =countif(type range, office type) (where type range is the cell range of all your =vlookup functions you created, and office type is the cell address for the office type (one cell to the left)). This will give you a count of the number of office types. Copy this formula down for each office type.
I can email you an example of the above description needs further clearification.

by peyison

You can do this using the DCount function. There are a few things you need to do to prepare your worksheet.

First, add a title row (if there's not one already). It should be the first row in the table (for this example I'm assuming that the tablestarts at A1). In B1, enter a title for the employee column - "employees".

Next you need to create some criteria ranges. In cells D1, E1, F1, G1, H1, I1, J1 enter the same title that's in B1 - "employees". Enter the following values:

D2: >0
E2: <11
F2: >10
G2: <21
H2: >20
I2: <31
J2: >30

The formulas would look like:

Type A: =DCOUNT(A1:B50,2,D1:E2)
Type B: =DCOUNT(A1:B50,2,F1:G2)
Type C: =DCOUNT(A1:B50,2,H1:I2)
Type D: =DCOUNT(A1:B50,2,J1:J2)

You could enter the column title "employees" in place of the 2 in the formulas above.

Hope this helps.

This discussion is closed to new replies.